package constants;

/**
 * 数据库S	QL模板
 * @author Administrator
 *
 */
public class SQLTempletes {

	/**
	 * 分页查询前缀
	 */
	public static final String PAGE_SELECT = "select ";
	
	/**
	 * 标别名
	 */
	public static final String TABLE_NAME = " t";
	
	/**
	 * 常规查询前缀
	 */
	public static final String SELECT = "select ";
	
	/**
	 * 用户基本信息
	 */
	public static final String V_USERS = "t_users.id AS id,t_users.time AS time,t_users.name AS name,t_users.photo AS photo,t_users.reality_name AS reality_name,t_users.password AS password,t_users.password_continuous_errors AS password_continuous_errors,t_users.is_password_error_locked AS is_password_error_locked,t_users.password_error_locked_time AS password_error_locked_time,t_users.pay_password AS pay_password,t_users.pay_password_continuous_errors AS pay_password_continuous_errors,t_users.is_pay_password_error_locked AS is_pay_password_error_locked,t_users.pay_password_error_locked_time AS pay_password_error_locked_time,t_users.is_secret_set AS is_secret_set,t_users.secret_set_time AS secret_set_time,t_users.is_allow_login AS is_allow_login,t_users.login_count AS login_count,t_users.last_login_time AS last_login_time,t_users.last_login_ip AS last_login_ip,t_users.last_logout_time AS last_logout_time,t_users.email AS email,t_users.is_email_verified AS is_email_verified,t_users.mobile1 AS mobile1,t_users.mobile AS mobile,t_users.is_mobile_verified AS is_mobile_verified,t_users.mobile2 AS mobile2,t_users.id_number AS id_number,t_users.address AS address,t_users.postcode AS postcode,t_users.sex AS sex,t_users.birthday AS birthday,t_users.city_id AS city_id,t_users.family_address AS family_address,t_users.family_telephone AS family_telephone,t_users.company AS company,t_users.company_address AS company_address,t_users.office_telephone AS office_telephone,t_users.fax_number AS fax_number,t_users.education_id AS education_id,t_users.marital_id AS marital_id,t_users.house_id AS house_id,t_users.car_id AS car_id,t_users.is_add_base_info AS is_add_base_info,t_users.is_erased AS is_erased,t_users.recommend_user_id AS recommend_user_id,t_users.recommend_reward_type AS recommend_reward_type,t_users.master_identity AS master_identity,t_users.vip_status AS vip_status,t_users.balance AS balance,t_users.balance2 AS balance2,t_users.freeze AS freeze,t_users.score AS score,t_users.credit_score AS credit_score,t_users.credit_level_id AS credit_level_id,t_users.is_refused_receive AS is_refused_receive,t_users.refused_time AS refused_time,t_users.refused_reason AS refused_reason,t_users.is_blacklist AS is_blacklist,t_users.joined_time AS joined_time,t_users.joined_reason AS joined_reason,t_users.assigned_time AS assigned_time,t_users.assigned_to_supervisor_id AS assigned_to_supervisor_id,t_users.email2 AS email2,t_users.telephone AS telephone,t_users.credit_line AS credit_line,t_users.last_credit_line AS last_credit_line,t_users.is_active AS is_active,t_users.ips_acct_no AS ips_acct_no,t_users.ips_bid_auth_no AS ips_bid_auth_no,t_users.ips_repay_auth_no AS ips_repay_auth_no,t_dict_ad_citys.name AS city_name,t_dict_ad_provinces.name AS province_name,ifnull(t_dict_ad_provinces.id,0) AS province_Id,t_dict_educations.name AS education_name,t_dict_houses.name AS house_name,t_dict_maritals.name AS marital_name,t_dict_cars.name AS car_name,t_users.lock_time AS lock_time,t_users.secret_question_id1 AS secret_question_id1,t_users.answer1 AS answer1,t_users.secret_question_id2 AS secret_question_id2,t_users.answer2 AS answer2,t_users.secret_question_id3 AS secret_question_id3,t_users.answer3 AS answer3,t_users.qr_code AS qr_code,question1.name AS question_name1,question2.name AS question_name2,question3.name AS question_name3 from t_users left join t_dict_ad_citys on t_users.city_id = t_dict_ad_citys.id left join t_dict_ad_provinces on t_dict_ad_citys.province_id = t_dict_ad_provinces.id left join t_dict_educations on t_users.education_id = t_dict_educations.id left join t_dict_houses on t_users.house_id = t_dict_houses.id left join t_dict_maritals on t_users.marital_id = t_dict_maritals.id left join t_dict_cars on t_users.car_id = t_dict_cars.id left join t_dict_secret_questions question1 on t_users.secret_question_id1 = question1.id left join t_dict_secret_questions question2 on t_users.secret_question_id2 = question2.id left join t_dict_secret_questions question3 on t_users.secret_question_id3 = question3.id where 1=1 ";
	
	/**
	 * 提现记录
	 */
	public static final String V_USER_WITHDRAWALS = "w.id AS id,w.user_id AS user_id,user.name AS name,w.amount AS amount,b.account AS account,b.bank_name AS bank_name,b.account_name AS account_name,w.time AS time,w.audit_time AS audit_time,w.pay_time AS pay_time,w.status AS status from t_user_withdrawals w join t_users user on w.user_id = user.id left join t_user_bank_accounts b on w.user_id = b.user_id and w.bank_account_id = b.id where 1=1 ";
	
	/**
	 * 提现详情
	 */
	public static final String V_USER_WITHDRAWAL_INFO = "w.id AS id,t_users.id AS user_id,t_users.name AS name,t_users.credit_score AS credit_score,(t_users.balance + t_users.freeze) AS user_amount,w.time AS time,w.type AS type,w.audit_time AS audit_time,w.pay_time AS pay_time,w.amount AS amount,(SELECT t_credit_levels.image_filename AS credit_level_image_filename FROM t_credit_levels WHERE (t_credit_levels.id = t_users.credit_level_id)) AS credit_level_image_filename,w.status AS status,(select ifnull(sum(((t_bills.repayment_corpus + t_bills.real_repayment_interest) + t_bills.overdue_fine)),0) AS repayment_amount from (t_bills join t_bids on((t_bills.bid_id = t_bids.id))) where ((t_bids.user_id = t_users.id) and (t_bills.status in (-1,-2)))) AS repayment_amount,(select ifnull(sum(((t_bill_invests.receive_corpus + t_bill_invests.receive_interest) + t_bill_invests.overdue_fine)),0) AS receive_amount from t_bill_invests where ((t_bill_invests.user_id = t_users.id) and (t_bill_invests.status in (-1,-2,-4)))) AS receive_amount from t_users join t_user_withdrawals w where t_users.id = w.user_id ";
	
	/**
	 * 等待满标的理财标
	 */
	public static final String V_USER_WAITING_FULL_INVEST_BIDS = "t_invests.id AS id,t_invests.amount AS invest_amount,t_bids.loan_schedule AS loan_schedule,t_bids.apr AS apr,t_bids.amount AS bid_amount,t_bids.status AS status,t_bids.title AS title,t_invests.bid_id AS bid_id,t_bids.period AS period,t_bids.period_unit AS period_unit,t_users.name AS name,t_invests.user_id AS user_id,t_products.name AS product_name,t_products.name_image_filename AS image_filename,f_user_audit_item(t_users.id,t_bids.mark,2) AS user_item_count,(select count(pail.id) AS product_item_count from t_product_audit_items_log pail where ((pail.mark = t_bids.mark) and (pail.type = 1))) AS product_item_count,concat((select t_system_options._value AS _value from t_system_options where t_system_options._key = 'loan_number'),(t_invests.bid_id + '')) AS no from t_invests left join t_bids on t_bids.id = t_invests.bid_id left join t_users on t_users.id = t_bids.user_id left join t_products on t_products.id = t_bids.product_id where t_bids.status in (1,2) ";
	
	/**
	 * 等待放款的理财标
	 */
	public static final String V_USER_INVEST_READY_RELEASE_BID = "t_invests.id AS id,t_invests.time AS time,t_invests.amount AS invest_amount,t_bids.loan_schedule AS loan_schedule,t_bids.apr AS apr,t_bids.amount AS bid_amount,t_bids.status AS status,t_bids.title AS title,t_invests.bid_id AS bid_id,t_bids.period AS period,t_bids.period_unit AS period_unit,t_users.name AS name,t_invests.user_id AS user_id,t_products.name AS product_name,t_products.name_image_filename AS image_filename,f_user_audit_item(t_users.id,t_bids.mark,2) AS user_item_count,(select count(pail.id) AS product_item_count from t_product_audit_items_log pail where ((pail.mark = t_bids.mark) and (pail.type = 1))) AS product_item_count,concat((select t_system_options._value AS _value from t_system_options where t_system_options._key = 'loan_number'),(t_invests.bid_id + '')) AS no from t_invests left join t_bids on t_bids.id = t_invests.bid_id left join t_users on t_users.id = t_bids.user_id left join t_products on t_products.id = t_bids.product_id where t_bids.status in (1, 3) ";
	
	/**
	 * 用户VIP信息
	 */
	public static final String V_USER_VIP_INFO = "t_users.id AS id,t_credit_levels.image_filename AS credit_level_image_filename,t_credit_levels.order_sort AS order_sort,t_users.name AS name,t_users.time AS register_time,t_users.credit_score AS credit_score,t_users.email AS email,t_users.mobile AS mobile,t_users.mobile1 AS mobile1,t_users.mobile2 AS mobile2,t_users.is_allow_login AS is_allow_login,((t_users.balance + t_users.freeze) + t_users.balance2) AS user_amount,(select t_user_vip_records.expiry_date AS expiry_date from t_user_vip_records where ((t_user_vip_records.user_id = t_users.id) and (t_user_vip_records.status = 1))) AS vip_expiry_date,(select ifnull(sum(t_user_details.amount),0) AS recharge_amount from t_user_details where ((t_user_details.user_id = t_users.id) and (t_user_details.operation in (1,2,3)))) AS recharge_amount,(select count(t_invests.bid_id) AS invest_count from t_invests where ((t_invests.user_id = t_users.id) and (t_invests.transfers_id = 0))) AS invest_count,(select ifnull(sum(t_invests.amount),0) AS invest_amount from t_invests where (t_invests.user_id = t_users.id)) AS invest_amount,(select count(0) AS bid_count from t_bids where (t_bids.user_id = t_users.id)) AS bid_count,(select ifnull(sum(t_bids.amount),0) AS bid_count from t_bids where (t_bids.user_id = t_users.id)) AS bid_amount,(select count(0) AS audit_item_count from t_user_audit_items where (t_users.id = t_user_audit_items.user_id and t_user_audit_items.status = 2)) AS audit_item_count from t_users LEFT JOIN t_credit_levels ON ((t_users.credit_level_id = t_credit_levels.id)) where t_users.vip_status = 1 ";
	
	/**
	 * 用户单个信息
	 */
	public static final String V_USER_USERS = "t_users.id AS id,t_users.name AS name,t_users.credit_score AS credit_score,t_users.credit_level_id AS credit_level_id,t_users.reality_name AS reality_name,t_users.time AS time,t_users.photo AS photo,t_users.email AS email,t_users.is_email_verified AS is_email_verified,t_users.mobile AS mobile,t_users.is_mobile_verified AS is_mobile_verified,t_users.id_number AS id_number,t_users.birthday AS birthday,t_users.sex AS sex,t_dict_ad_citys.name AS city_name,t_dict_ad_provinces.name AS province_name,t_dict_educations.name AS education_name,t_dict_maritals.name AS marital_name,t_dict_houses.name AS house_name,t_dict_cars.name AS car_name from t_users left join t_dict_ad_citys on t_users.city_id = t_dict_ad_citys.id left join t_dict_ad_provinces on t_dict_ad_citys.province_id = t_dict_ad_provinces.id left join t_dict_educations on t_users.education_id = t_dict_educations.id left join t_dict_maritals on t_users.marital_id = t_dict_maritals.id left join t_dict_houses on t_users.house_id = t_dict_houses.id left join t_dict_cars on t_users.car_id = t_dict_cars.id where 1=1 ";

	/**
	 * 未确认邮件的用户信息
	 */
	public static final String V_USER_UNVERIFIED_INFO = "t_users.id AS id,t_users.name AS name,t_users.time AS register_time,t_users.credit_score AS credit_score,t_users.email AS email,t_users.mobile AS mobile,t_users.is_allow_login AS is_allow_login,(SELECT t_credit_levels.image_filename AS credit_level_image_filename FROM t_credit_levels WHERE (t_credit_levels.id = t_users.credit_level_id)) AS credit_level_image_filename,(t_users.balance + t_users.freeze) AS user_amount,(select ifnull(sum(t_user_details.amount),0) AS recharge_amount from t_user_details where ((t_user_details.user_id = t_users.id) and (t_user_details.operation in (1,2,3)))) AS recharge_amount,(select count(t_invests.bid_id) AS invest_count from t_invests where ((t_invests.user_id = t_users.id) and (t_invests.transfers_id = 0))) AS invest_count,(select ifnull(sum(t_invests.amount),0) AS invest_amount from t_invests where (t_invests.user_id = t_users.id)) AS invest_amount from t_users where t_users.is_email_verified = 0 ";
	
	/**
	 * 用户成功投资标的
	 */
	public static final String V_USER_SUCCESS_INVEST_BIDS = "`t_invests`.`id` AS `id`,`t_invests`.`user_id` AS `user_id`,`t_invests`.`bid_id` AS `bid_id`,`t_invests`.`amount` AS `invest_amount`,`t_users`.`name` AS `name`,`t_bids`.`title` AS `title`,`t_bids`.`amount` AS `bid_amount`,`t_bids`.`apr` AS `apr`,concat((select `t_system_options`.`_value` AS `_value` from `t_system_options` where (`t_system_options`.`_key` = 'loan_number')),(`t_invests`.`bid_id` + '')) AS `no`,(select ifnull(count(`t_bill_invests`.`id`),0) AS `IFNULL(COUNT(id),0)` from `t_bill_invests` where ((`t_invests`.`id` = `t_bill_invests`.`invest_id`) and (`t_bill_invests`.`status` = -(2)))) AS `overdue_payback_period`,(select ifnull(sum(((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`)),0) AS `receiving_amount` from `t_bill_invests` where (`t_invests`.`id` = `t_bill_invests`.`invest_id`)) AS `receiving_amount`,`t_bids`.`last_repay_time` AS `last_repay_time` from ((`t_invests` left join `t_bids` on((`t_bids`.`id` = `t_invests`.`bid_id`))) left join `t_users` on((`t_users`.`id` = `t_bids`.`user_id`))) where ((`t_bids`.`status` = 5 or `t_bids`.`status` = 14) and (`t_invests`.`transfer_status` <> -(1))) ";
	
	/**
	 * 用户积分
	 */
	public static final String V_USER_SCORES = "t_users.id AS id,t_users.score AS score,t_users.credit_score AS credit_score from t_users where 1=1 ";
	
	/**
	 * 用户举报信箱
	 */
	public static final String V_USER_REPORTED_INFO = "`t_users`.`id` AS `id`,`t_users`.`name` AS `name`,`t_users`.`time` AS `register_time`,`t_users`.`credit_score` AS `credit_score`,`t_users`.`email` AS `email`,`t_users`.`mobile` AS `mobile`,`t_users`.`mobile1` AS `mobile1`,`t_users`.`mobile2` AS `mobile2`,`t_users`.`is_allow_login` AS `is_allow_login`,(SELECT t_credit_levels.image_filename AS credit_level_image_filename FROM t_credit_levels WHERE (t_credit_levels.id = t_users.credit_level_id)) AS credit_level_image_filename,((`t_users`.`balance` + `t_users`.`freeze`) + `t_users`.`balance2`) AS `user_amount`,(select ifnull(sum(`t_user_details`.`amount`),0) AS `recharge_amount` from `t_user_details` where ((`t_user_details`.`user_id` = `t_users`.`id`) and (`t_user_details`.`operation` in (1,2,3)))) AS `recharge_amount`,(select count(`t_invests`.`bid_id`) AS `invest_count` from `t_invests` where ((`t_invests`.`user_id` = `t_users`.`id`) and (`t_invests`.`transfers_id` = 0))) AS `invest_count`,(select ifnull(sum(`t_invests`.`amount`),0) AS `invest_amount` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_amount`,(select count(0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_count`,(select ifnull(sum(`t_bids`.`amount`),0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_amount`,(select count(0) AS `overdue_bill_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` < 0))) AS `overdue_bill_count`,(select count(0) AS `count(*)` from `t_user_report_users` where (`t_user_report_users`.`reported_user_id` = `t_users`.`id`)) AS `reported_count` from `t_users` where `t_users`.`id` in (select distinct `t_user_report_users`.`reported_user_id` AS `reported_user_id` from `t_user_report_users`) ";
	
	public static final String V_USER_REPORTED_INFO_COUNT = " select count(1) as result from `t_users` where `t_users`.`id` in (select distinct `t_user_report_users`.`reported_user_id` AS `reported_user_id` from `t_user_report_users`) ";
	
	/**
	 * 用户举报列表
	 */
	public static final String V_USER_REPORT_LIST = "t_user_report_users.id AS id,t_user_report_users.user_id AS user_id,t_users.name AS name,t_users.mobile AS mobile,t_user_report_users.reason AS reason,t_user_report_users.reported_user_id AS reported_user_id,t_bids.title AS bid_title,t_invest_transfers.title AS invest_transfer_title from t_user_report_users join t_users on t_user_report_users.user_id = t_users.id left join t_bids on t_user_report_users.relation_bid_id = t_bids.id left join t_invest_transfers on t_user_report_users.relation_invest_transfer_id = t_invest_transfers.id where 1=1 ";
	
	/**
	 * 用户逾期借款
	 */
	public static final String V_USER_OVER_BORROWS = "`uob`.`id` AS `id`,`uob`.`amount` AS `amount`,`uob`.`pass_amount` AS `pass_amount`,`uob`.`time` AS `time`,`uob`.`credit_line` AS `credit_line`,(case `uob`.`status` when 0 then '审核中' when 1 then '通过' when 2 then '未通过' else '其他' end) AS `status`,`uob`.`user_id` AS `user_id`,`uob`.`reason` AS `reason`,`u`.`name` AS `user_name`,`u`.`email` AS `user_email`,`u`.`mobile` AS `user_mobile`,`u`.`last_credit_line` AS `user_credit_line`,`uob`.`audit_opinion` AS `audit_opinion`,(select count(0) AS `COUNT(*)` from `t_user_audit_items` `uai` where ((`uai`.`over_borrow_id` = `uob`.`id`) and (`uai`.`user_id` = `u`.`id`) and (`uai`.`status` = 2))) AS `passed_items_count`,(select count(0) AS `COUNT(*)` from `t_user_audit_items` `uai` where ((`uai`.`over_borrow_id` = `uob`.`id`) and (`uai`.`user_id` = `u`.`id`) and (`uai`.`status` = -(1)))) AS `unpassed_items_count`,(select count(0) AS `COUNT(*)` from `t_user_audit_items` `uai` where ((`uai`.`over_borrow_id` = `uob`.`id`) and (`uai`.`user_id` = `u`.`id`) and (`uai`.`status` = 1))) AS `auditing_items_count`,(select count(0) AS `COUNT(*)` from `t_user_audit_items` `uai` where ((`uai`.`over_borrow_id` = `uob`.`id`) and (`uai`.`user_id` = `u`.`id`))) AS `appended_items_count` from (`t_user_over_borrows` `uob` left join `t_users` `u` on((`uob`.`user_id` = `u`.`id`))) where 1=1 ";
	
	/**
	 * 锁定用户信息
	 */
	public static final String V_USER_LOCKED_INFO = "`t_users`.`id` AS `id`,`t_users`.`name` AS `name`,`t_users`.`time` AS `register_time`,`t_users`.`credit_score` AS `credit_score`,`t_users`.`email` AS `email`,`t_users`.`mobile` AS `mobile`,`t_users`.`mobile1` AS `mobile1`,`t_users`.`mobile2` AS `mobile2`,`t_users`.`is_allow_login` AS `is_allow_login`,`t_users`.`is_blacklist` AS `is_blacklist`,`t_users`.`lock_time` AS `lock_time`,(SELECT t_credit_levels.image_filename AS credit_level_image_filename FROM t_credit_levels WHERE (t_credit_levels.id = t_users.credit_level_id)) AS credit_level_image_filename,((`t_users`.`balance` + `t_users`.`freeze`) + `t_users`.`balance2`) AS `user_amount`,(select ifnull(sum(`t_user_details`.`amount`),0) AS `recharge_amount` from `t_user_details` where ((`t_user_details`.`user_id` = `t_users`.`id`) and (`t_user_details`.`operation` in (1,2,3)))) AS `recharge_amount`,(select count(`t_invests`.`bid_id`) AS `invest_count` from `t_invests` where ((`t_invests`.`user_id` = `t_users`.`id`) and (`t_invests`.`transfers_id` = 0))) AS `invest_count`,(select ifnull(sum(`t_invests`.`amount`),0) AS `invest_amount` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_amount`,(select count(0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_count`,(select ifnull(sum(`t_bids`.`amount`),0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_amount`,(select count(0) AS `overdue_bill_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` < 0))) AS `overdue_bill_count`,(select ifnull(sum(((`t_bills`.`repayment_corpus` + `t_bills`.`real_repayment_interest`) + `t_bills`.`overdue_fine`)),0) AS `repayment_amount` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` in (-(1),-(2))))) AS `repayment_amount` from `t_users` where `t_users`.`is_allow_login` = 1 ";
	
	/**
	 * 未标注的贷款用户
	 */
	public static final String V_USER_LOAN_USER_UNASSIGNED = "distinct `t_bids`.`id` AS `id`,`t_bids`.`product_id` AS `product_id`,concat((select `t_system_options`.`_value` AS `_value` from `t_system_options` where (`t_system_options`.`_key` = 'loan_number')),`t_bids`.`id`) AS `bid_no`,(case when ((select count(0) from `t_bids` `b` where ((`b`.`user_id` = `t_users`.`id`) and (`b`.`manage_supervisor_id` > 0))) > 0) then 1 else 2 end) AS `type`,`t_users`.`id` AS `user_id`,`t_users`.`name` AS `name`,`t_users`.`time` AS `register_time`,(select `t_products`.`name` AS `name` from `t_products` where (`t_products`.`id` = `t_bids`.`product_id`)) AS `product_name`,`t_bids`.`amount` AS `amount`,`t_bids`.`audit_time` AS `audit_time`,`t_users`.`vip_status` AS `vip_status`,`t_users`.`birthday` AS `birthday`,((year(curdate()) - year(`t_users`.`birthday`)) - (right(curdate(),5) < right(`t_users`.`birthday`,5))) AS `age`,(select `city`.`name` AS `name` from `t_dict_ad_citys` `city` where (`city`.`id` = `t_users`.`city_id`)) AS `city_name`,(select `province`.`name` AS `name` from (`t_dict_ad_citys` `city` join `t_dict_ad_provinces` `province` on((`city`.`province_id` = `province`.`id`))) where (`city`.`id` = `t_users`.`city_id`)) AS `province_name`,`t_users`.`sex` AS `sex`,t_credit_levels.image_filename AS credit_level_image_filename from (((`t_bids` join `t_users` on((`t_bids`.`user_id` = `t_users`.`id`))) JOIN t_credit_levels ON ((t_credit_levels.id = t_users.credit_level_id))) join `t_bills` on((`t_bids`.`id` = `t_bills`.`bid_id`))) where `t_bids`.`manage_supervisor_id` <= 0 and `t_users`.`assigned_to_supervisor_id` <= 0 ";
    public static final String V_USER_LOAN_USER_UNASSIGNED_COUNT = "SELECT  COUNT(DISTINCT t_bids.id) FROM((`t_bids`JOIN `t_users` ON ((`t_bids`.`user_id` = `t_users`.`id`))  )JOIN `t_bills` ON (    (`t_bids`.`id` = `t_bills`.`bid_id`)))WHERE `t_bids`.`manage_supervisor_id` <= 0 AND `t_users`.`assigned_to_supervisor_id` <= 0";
	
	/**
	 * 用户贷款账单详情
	 */
	public static final String V_USER_LOAN_INFO_BILL_D = "`t_users`.`id` AS `id`,`t_users`.`assigned_to_supervisor_id` AS `supervisor_id`,`t_supervisors`.`name` AS `supervisor_name`,`t_users`.`name` AS `name`,`t_users`.`time` AS `register_time`,(`t_users`.`balance` + `t_users`.`freeze`) AS `user_amount`,`t_users`.`last_login_time` AS `last_login_time`,(SELECT t_credit_levels.image_filename AS credit_level_image_filename FROM t_credit_levels WHERE (t_credit_levels.id = t_users.credit_level_id)) AS credit_level_image_filename,(select count(0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_count`,(select ifnull(sum(`t_bids`.`amount`),0) AS `bid_count` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` in (4,5)))) AS `bid_amount`,(select count(`t_invests`.`bid_id`) AS `invest_count` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_count`,(select ifnull(sum(`t_invests`.`amount`),0) AS `invest_amount` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_amount`,(select count(0) AS `bid_loaning_amount` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` = 2))) AS `bid_loaning_amount`,(select count(0) AS `bid_repayment_amount` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` = 4))) AS `bid_repayment_amount`,(select count(0) AS `overdue_bill_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` < 0))) AS `overdue_bill_count`,(select count(distinct `t_bids`.`id`) AS `bad_bid_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` = -(3)))) AS `bad_bid_count` from (`t_users` join `t_supervisors` on((`t_users`.`assigned_to_supervisor_id` = `t_supervisors`.`id`))) where `t_users`.`assigned_to_supervisor_id` > 0 and `t_users`.`master_identity` in (1,3) ";
	
	/**
	 * 用户贷款账单
	 */
	public static final String V_USER_LOAN_INFO_BILL = "`t_users`.`id` AS `id`,`supervisor`.`id` AS `supervisor_id`,'1' AS `type`,`t_users`.`name` AS `name`,`t_users`.`time` AS `register_time`,((`t_users`.`balance` + `t_users`.`freeze`) + `t_users`.`balance2`) AS `user_amount`,`t_users`.`last_login_time` AS `last_login_time`,(SELECT t_credit_levels.image_filename AS credit_level_image_filename FROM t_credit_levels WHERE (t_credit_levels.id = t_users.credit_level_id)) AS credit_level_image_filename,(select count(0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_count`,(select ifnull(sum(`t_bids`.`amount`),0) AS `bid_amount` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` in (4,5)))) AS `bid_amount`,(select count(`t_invests`.`bid_id`) AS `invest_count` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_count`,(select ifnull(sum(`t_invests`.`amount`),0) AS `invest_amount` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_amount`,(select count(0) AS `bid_loaning_amount` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` = 2))) AS `bid_loaning_amount`,(select count(0) AS `bid_repayment_amount` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` = 4))) AS `bid_repayment_amount`,(select count(0) AS `overdue_bill_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` < 0))) AS `overdue_bill_count`,(select count(distinct `t_bids`.`id`) AS `bad_bid_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` = -(3)))) AS `bad_bid_count` from (`t_supervisors` `supervisor` join `t_users` on((`supervisor`.`id` = `t_users`.`assigned_to_supervisor_id`))) where (`t_users`.`master_identity` in (1,3)) union select distinct `t_users`.`id` AS `id`,`supervisor`.`id` AS `supervisor_id`,'2' AS `type`,`t_users`.`name` AS `name`,`t_users`.`time` AS `register_time`,(`t_users`.`balance` + `t_users`.`freeze`) AS `user_amount`,`t_users`.`last_login_time` AS `last_login_time`,(SELECT t_credit_levels.image_filename AS credit_level_image_filename FROM t_credit_levels WHERE (t_credit_levels.id = t_users.credit_level_id)) AS credit_level_image_filename,(select count(0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_count`,(select ifnull(sum(`t_bids`.`amount`),0) AS `bid_count` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` in (4,5)))) AS `bid_amount`,(select count(`t_invests`.`bid_id`) AS `invest_count` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_count`,(select ifnull(sum(`t_invests`.`amount`),0) AS `invest_amount` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_amount`,(select count(0) AS `bid_loaning_amount` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` = 2))) AS `bid_loaning_amount`,(select count(0) AS `bid_repayment_amount` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` = 4))) AS `bid_repayment_amount`,(select count(0) AS `overdue_bill_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` < 0))) AS `overdue_bill_count`,(select count(distinct `t_bids`.`id`) AS `bad_bid_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` = -(3)))) AS `bad_bid_count` from ((`t_supervisors` `supervisor` join `t_bids` `bid` on((`supervisor`.`id` = `bid`.`manage_supervisor_id`))) join `t_users` on((`bid`.`user_id` = `t_users`.`id`))) where `t_users`.`master_identity` in (1,3) ";
	
	/**
	 * 用户贷款坏账详情
	 */
	public static final String V_USER_LOAN_INFO_BAD_D = "`t_users`.`id` AS `id`,`supervisor`.`name` AS `supervisor_name`,'1' AS `type`,`t_users`.`name` AS `name`,`t_users`.`time` AS `register_time`,((`t_users`.`balance` + `t_users`.`freeze`) + `t_users`.`balance2`) AS `user_amount`,`t_users`.`last_login_time` AS `last_login_time`,(SELECT t_credit_levels.image_filename AS credit_level_image_filename FROM t_credit_levels WHERE (t_credit_levels.id = t_users.credit_level_id)) AS credit_level_image_filename,(select count(0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_count`,(select ifnull(sum(`t_bids`.`amount`),0) AS `bid_count` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` in (4,5)))) AS `bid_amount`,(select count(`t_invests`.`bid_id`) AS `invest_count` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_count`,(select ifnull(sum(`t_invests`.`amount`),0) AS `invest_amount` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_amount`,(select count(0) AS `bid_loaning_amount` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` = 2))) AS `bid_loaning_amount`,(select count(0) AS `bid_repayment_amount` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` = 4))) AS `bid_repayment_amount`,(select count(0) AS `overdue_bill_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` < 0))) AS `overdue_bill_count`,(select count(distinct `t_bids`.`id`) AS `bad_bid_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` = -(3)))) AS `bad_bid_count`,(select ifnull(sum(((`t_bills`.`repayment_corpus` + `t_bills`.`repayment_interest`) + `t_bills`.`overdue_fine`)),0) AS `bad_bid_amount` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`overdue_mark` = -(3)))) AS `bad_bid_amount` from (((`t_users` join `t_bids` on((`t_users`.`id` = `t_bids`.`user_id`))) join `t_bills` on((`t_bills`.`bid_id` = `t_bids`.`id`))) left join `t_supervisors` `supervisor` on((`t_users`.`assigned_to_supervisor_id` = `supervisor`.`id`))) where `t_users`.`master_identity` in (1,3) and `t_bills`.`overdue_mark` = -(3) ";
	
	/**
	 * 用户贷款坏账
	 */
	public static final String V_USER_LOAN_INFO_BAD = "`t_users`.`id` AS `id`,`supervisor`.`id` AS `supervisor_id`,'1' AS `type`,`t_users`.`name` AS `name`,`t_users`.`time` AS `register_time`,((`t_users`.`balance` + `t_users`.`freeze`) + `t_users`.`balance2`) AS `user_amount`,`t_users`.`last_login_time` AS `last_login_time`,(SELECT t_credit_levels.image_filename AS credit_level_image_filename FROM t_credit_levels WHERE (t_credit_levels.id = t_users.credit_level_id)) AS credit_level_image_filename,(select count(0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_count`,(select ifnull(sum(`t_bids`.`amount`),0) AS `bid_count` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` in (4,5)))) AS `bid_amount`,(select count(`t_invests`.`bid_id`) AS `invest_count` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_count`,(select ifnull(sum(`t_invests`.`amount`),0) AS `invest_amount` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_amount`,(select count(0) AS `bid_loaning_amount` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` = 2))) AS `bid_loaning_amount`,(select count(0) AS `bid_repayment_amount` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` = 4))) AS `bid_repayment_amount`,(select count(0) AS `overdue_bill_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` < 0))) AS `overdue_bill_count`,(select count(distinct `t_bids`.`id`) AS `bad_bid_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`overdue_mark` = -(3)))) AS `bad_bid_count` from (((`t_supervisors` `supervisor` join `t_users` on((`supervisor`.`id` = `t_users`.`assigned_to_supervisor_id`))) join `t_bids` on((`t_users`.`id` = `t_bids`.`user_id`))) join `t_bills` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_users`.`master_identity` in (1,3)) and (`t_bills`.`status` = -(1))) union select distinct `t_users`.`id` AS `id`,`supervisor`.`id` AS `supervisor_id`,'2' AS `type`,`t_users`.`name` AS `name`,`t_users`.`time` AS `register_time`,(`t_users`.`balance` + `t_users`.`freeze`) AS `user_amount`,`t_users`.`last_login_time` AS `last_login_time`,(SELECT t_credit_levels.image_filename AS credit_level_image_filename FROM t_credit_levels WHERE (t_credit_levels.id = t_users.credit_level_id)) AS credit_level_image_filename,(select count(0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_count`,(select ifnull(sum(`t_bids`.`amount`),0) AS `bid_count` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` in (4,5)))) AS `bid_amount`,(select count(`t_invests`.`bid_id`) AS `invest_count` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_count`,(select ifnull(sum(`t_invests`.`amount`),0) AS `invest_amount` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_amount`,(select count(0) AS `bid_loaning_amount` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` = 2))) AS `bid_loaning_amount`,(select count(0) AS `bid_repayment_amount` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` = 4))) AS `bid_repayment_amount`,(select count(0) AS `overdue_bill_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` < 0))) AS `overdue_bill_count`,(select count(distinct `t_bids`.`id`) AS `bad_bid_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` = -(3)))) AS `bad_bid_count` from ((((`t_supervisors` `supervisor` join `t_bids` `bid` on((`supervisor`.`id` = `bid`.`manage_supervisor_id`))) join `t_users` on((`bid`.`user_id` = `t_users`.`id`))) join `t_bids` on((`t_users`.`id` = `t_bids`.`user_id`))) join `t_bills` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where `t_users`.`master_identity` in (1,3) and `t_bills`.`overdue_mark` = -(3) ";
	
	/**
	 * 用户贷款信息
	 */
	public static final String V_USER_LOAN_INFO = "`t_users`.`id` AS `id`,`t_users`.`name` AS `name`,`t_users`.`time` AS `register_time`,`t_users`.`credit_score` AS `credit_score`,`t_users`.`email` AS `email`,`t_users`.`mobile` AS `mobile`,`t_users`.`mobile1` AS `mobile1`,`t_users`.`mobile2` AS `mobile2`,`t_users`.`is_allow_login` AS `is_allow_login`,t_credit_levels.image_filename AS credit_level_image_filename,((`t_users`.`balance` + `t_users`.`freeze`) + `t_users`.`balance2`) AS `user_amount`,(select count(0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_count`,(select ifnull(sum(`t_bids`.`amount`),0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_amount`,(select ifnull(round(avg(`t_bids`.`apr`),2),0) AS `avg_apr` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `avg_apr`,(select count(0) AS `bid_loaning_count` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` = 2))) AS `bid_loaning_count`,(select count(0) AS `bid_repayment_count` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` = 4))) AS `bid_repayment_count`,(select count(0) AS `overdue_bill_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` < 0))) AS `overdue_bill_count`,(select count(distinct `t_bids`.`id`) AS `bad_bid_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` = -(3)))) AS `bad_bid_count`,(select count(0) AS `audit_item_count` from `t_user_audit_items` where ((`t_users`.`id` = `t_user_audit_items`.`user_id`) and (`t_user_audit_items`.`status` = 2))) AS `audit_item_count`,(select ifnull(sum(((`t_bills`.`repayment_corpus` + `t_bills`.`real_repayment_interest`) + `t_bills`.`overdue_fine`)),0) AS `repayment_amount` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` in (-(1),-(2))))) AS `repayment_amount`,(select `t_supervisors`.`name` AS `supervisor_name` from `t_supervisors` where (`t_supervisors`.`id` = `t_users`.`assigned_to_supervisor_id`)) AS `supervisor_name` from `t_users` LEFT JOIN t_credit_levels ON t_users.credit_level_id = t_credit_levels.id where `t_users`.`master_identity` in (1,3) ";
	
	/**
	 * 用户投资信息
	 */
	public static final String V_USER_INVEST_INFO = "`t_users`.`id` AS `id`,t_credit_levels.image_filename AS credit_level_image_filename,t_credit_levels.order_sort AS order_sort,`t_users`.`name` AS `name`,`t_users`.`time` AS `register_time`,`t_users`.`credit_score` AS `credit_score`,`t_users`.`email` AS `email`,`t_users`.`mobile` AS `mobile`,`t_users`.`mobile1` AS `mobile1`,`t_users`.`mobile2` AS `mobile2`,`t_users`.`is_allow_login` AS `is_allow_login`,`t_users`.`master_identity` AS `master_identity`,((`t_users`.`balance` + `t_users`.`freeze`) + `t_users`.`balance2`) AS `user_amount`,(select ifnull(sum(`t_user_details`.`amount`),0) AS `recharge_amount` from `t_user_details` where ((`t_user_details`.`user_id` = `t_users`.`id`) and (`t_user_details`.`operation` in (1,2,3)))) AS `recharge_amount`,(select count(`t_invests`.`bid_id`) AS `invest_count` from `t_invests` where ((`t_invests`.`user_id` = `t_users`.`id`) and (`t_invests`.`transfers_id` = 0))) AS `invest_count`,(select ifnull(sum(`t_invests`.`amount`),0) AS `invest_amount` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_amount`,(select count(0) AS `invest_receive_count` from `t_bids` `bid` where ((`bid`.`status` = 4) and `bid`.`id` in (select `inv`.`bid_id` from `t_invests` `inv` where ((`inv`.`user_id` = `t_users`.`id`) and (`inv`.`transfer_status` <> -(1)))))) AS `invest_receive_count`,(select ifnull(sum(((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`)),0) AS `receive_amount` from `t_bill_invests` where ((`t_bill_invests`.`user_id` = `t_users`.`id`) and (`t_bill_invests`.`status` in (-(1),-(2),-(4))))) AS `receive_amount`,(select count(0) AS `transfer_count` from `t_invests` where ((`t_invests`.`user_id` = `t_users`.`id`) and (`t_invests`.`transfer_status` = 1))) AS `transfer_count` from `t_users` LEFT JOIN t_credit_levels ON t_users.credit_level_id = t_credit_levels.id where `t_users`.`master_identity` in (2,3) ";
	
	/**
	 * 用户投资金额
	 */
	public static final String V_USER_INVEST_AMOUNT = "`t_users`.`id` AS `id`,(select count(0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_count`,(select count(`t_invests`.`bid_id`) AS `invest_count` from `t_invests` where ((`t_invests`.`user_id` = `t_users`.`id`) and (`t_invests`.`transfers_id` = 0))) AS `invest_count`,(select count(0) AS `count(*)` from `t_invests` where ((`t_invests`.`user_id` = `t_users`.`id`) and (`t_invests`.`transfer_status` = -(1)))) AS `transfer_count`,(select ifnull(sum(`t_invests`.`amount`),0) AS `invest_amount` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_amount`,(select ifnull(sum((`t_bill_invests`.`real_receive_interest` + `t_bill_invests`.`overdue_fine`)),0) AS `invest_interest` from `t_bill_invests` where (`t_bill_invests`.`user_id` = `t_users`.`id`)) AS `invest_interest` from `t_users` where 1=1 ";
	
	/**
	 * 用户信息
	 */
	public static final String V_USER_INFO = "`t_users`.`id` AS `id`,t_credit_levels.image_filename AS credit_level_image_filename,t_credit_levels.order_sort AS order_sort,`t_users`.`name` AS `name`,`t_users`.`time` AS `register_time`,`t_users`.`credit_score` AS `score`,`t_users`.`email` AS `email`,`t_users`.`mobile` AS `mobile`,`t_users`.`mobile1` AS `mobile1`,`t_users`.`mobile2` AS `mobile2`,`t_users`.`is_email_verified` AS `is_activation`,`t_users`.`is_blacklist` AS `is_blacklist`,((`t_users`.`balance` + `t_users`.`freeze`) + `t_users`.`balance2`) AS `user_amount`,`t_users`.`last_login_time` AS `last_login_time`,`t_users`.`is_allow_login` AS `is_allow_login`,(select ifnull(sum(`t_user_details`.`amount`),0) AS `recharge_amount` from (`t_user_details` join `t_user_detail_types` on((`t_user_details`.`operation` = `t_user_detail_types`.`id`))) where ((`t_user_details`.`user_id` = `t_users`.`id`) and (`t_user_detail_types`.`id` in (1,2,3)))) AS `recharge_amount`,(select count(`t_invests`.`bid_id`) AS `invest_count` from `t_invests`,t_bids where (`t_invests`.`user_id` = `t_users`.`id` AND t_invests.bid_id = t_bids.id AND t_bids.`status` IN (?, ?, ?))) AS `invest_count`,(select ifnull(sum(`t_invests`.`amount`),0) AS `invest_amount` from `t_invests`,t_bids where (`t_invests`.`user_id` = `t_users`.`id` AND t_invests.bid_id = t_bids.id AND t_bids.`status` IN (?, ?, ?))) AS `invest_amount`,(select count(0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id` AND t_bids.`status` IN (?, ?, ?))) AS `bid_count`,(select ifnull(sum(`t_bids`.`amount`),0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id` AND t_bids.`status` IN (?, ?, ?))) AS `bid_amount`,(select count(0) AS `audit_item_count` from `t_user_audit_items` where ((`t_users`.`id` = `t_user_audit_items`.`user_id`) and (`t_user_audit_items`.`status` = 2))) AS `audit_item_count` from `t_users` LEFT JOIN t_credit_levels ON t_users.credit_level_id = t_credit_levels.id where 1=1 ";
	
	/**
	 * 用户个人信息
	 */
	public static final String V_USER_FOR_PERSONAL = "`t_users`.`id` AS `id`,`t_users`.`name` AS `name`,`t_users`.`time` AS `time`,`t_users`.`photo` AS `photo`,`t_users`.`last_login_time` AS `last_login_time`,(select `city`.`name` AS `name` from `t_dict_ad_citys` `city` where (`city`.`id` = `t_users`.`city_id`)) AS `cityName`,(select `province`.`name` AS `name` from (`t_dict_ad_citys` `city` left join `t_dict_ad_provinces` `province` on((`city`.`province_id` = `province`.`id`))) where (`city`.`id` = `t_users`.`city_id`)) AS `provinceName`,(select `vip`.`expiry_date` AS `expiry_date` from `t_user_vip_records` `vip` where ((`vip`.`user_id` = `t_users`.`id`) and (`vip`.`status` = 1))) AS `expiry_date`,(select count(`t_invests`.`bid_id`) AS `invest_count` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_count`,(select count(0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_count` from `t_users` where 1=1 ";
	
	/**
	 *  用户消息
	 */
	public static final String V_USER_FOR_MESSAGE = "`attention`.`id` AS `id`,`attention`.`user_id` AS `user_id`,`user`.`name` AS `name`,`user`.`photo` AS `photo`,`attention`.`note_name` AS `note_name` from `t_user_attention_users` `attention` join `t_users` `user` on `attention`.`attention_user_id` = `user`.`id` where 1=1 ";
	
	/**
	 * 用户详情
	 */
	public static final String V_USER_FOR_DETAILS = "`t_users`.`id` AS `id`,`t_users`.`balance` AS `user_amount`,`t_users`.`balance2` AS `user_amount2`,`t_users`.`freeze` AS `freeze`,`t_users`.`credit_line` AS `credit_line`,(select ifnull(sum(((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`)),0) AS `receive_amount` from `t_bill_invests` where ((`t_bill_invests`.`user_id` = `t_users`.`id`) and (`t_bill_invests`.`status` in (-(1),-(2),-(5),-(6))))) AS `receive_amount` from `t_users` where 1=1 ";
	
	/**
	 * 用户明细资料
	 */
	public static final String V_USER_DETAILS = "`t_user_details`.`id` AS `id`,`t_users`.`id` AS `user_id`,`t_user_details`.`time` AS `time`,`t_user_details`.`operation` AS `operation`,`t_user_details`.`amount` AS `amount`,sum((`t_user_details`.`balance` + `t_user_details`.`freeze`)) AS `user_balance`,`t_user_details`.`balance` AS `balance`,`t_user_details`.`freeze` AS `freeze`,ifnull(`t_user_details`.`recieve_amount`,0) AS `recieve_amount`,`t_user_details`.`summary` AS `summary`,`t_user_detail_types`.`name` AS `name`,ifnull(`t_user_detail_types`.`type`,0) AS `type` from ((`t_users` join `t_user_details` on((`t_user_details`.`user_id` = `t_users`.`id`))) left join `t_user_detail_types` on((`t_user_details`.`operation` = `t_user_detail_types`.`id`))) where 1 = 1 ";
	
	/**
	 * 用户积分详情
	 */
	public static final String V_USER_DETAIL_SCORE = "`t_users`.`id` AS `id`,ifnull(sum((case when (`score`.`operation` = 1) then `score`.`score` end)),0) AS `audit_score`,ifnull(sum((case when (`score`.`operation` = 2) then `score`.`score` end)),0) AS `repayment_score`,ifnull(sum((case when (`score`.`operation` = 3) then `score`.`score` end)),0) AS `loan_score`,ifnull(sum((case when (`score`.`operation` = 4) then `score`.`score` end)),0) AS `invest_score`,ifnull(sum((case when (`score`.`operation` = -(1)) then `score`.`score` end)),0) AS `overdue_score` from `t_users` left join `t_user_details_credit_score` `score` on `t_users`.`id` = `score`.`user_id` where 1=1 ";
	
	/**
	 * 用户逾期的信用积分
	 */
	public static final String V_USER_DETAIL_CREDIT_SCORE_OVERDUE = "`score`.`id` AS `id`,`score`.`user_id` AS `user_id`,`score`.`score` AS `score`,(select concat(`options`.`_value`,`t_bids`.`id`) AS `CONCAT(``options``._value,t_bids.id)` from `t_system_options` `options` where (`options`.`_key` = 'loan_number')) AS `bid_no`,`t_bids`.`title` AS `title`,`t_bids`.`audit_time` AS `audit_time`,concat(`t_bills`.`periods`,'/',`t_bids`.`period`) AS `period` from (((`t_user_details_credit_score` `score` left join `t_users` on((`t_users`.`id` = `score`.`user_id`))) left join `t_bills` on((`score`.`relation_id` = `t_bills`.`id`))) left join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where `score`.`operation` = -(1) ";
	
	/**
	 * 用户正常还款信用积分
	 */
	public static final String V_USER_DETAIL_CREDIT_SCORE_NORMAL_REPAYMENT = "`score`.`id` AS `id`,`t_users`.`id` AS `user_id`,`score`.`score` AS `score`,(select concat(`options`.`_value`,`t_bids`.`id`) AS `CONCAT(``options``._value,t_bids.id)` from `t_system_options` `options` where (`options`.`_key` = 'loan_number')) AS `bid_no`,`t_bids`.`title` AS `title`,`t_bids`.`audit_time` AS `audit_time`,concat(`t_bills`.`periods`,'/',`t_bids`.`period`) AS `period` from (((`t_user_details_credit_score` `score` left join `t_users` on(((`t_users`.`id` = `score`.`user_id`) and (`score`.`operation` = 2)))) left join `t_bills` on((`score`.`relation_id` = `t_bills`.`id`))) left join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where 1=1 ";
	
	/**
	 * 用户贷款信用积分
	 */
	public static final String V_USER_DETAIL_CREDIT_SCORE_LOAN = "`t_user_details_credit_score`.`id` AS `id`,`t_users`.`id` AS `user_id`,(select concat(`options`.`_value`,`t_bids`.`id`) AS `CONCAT(``options``._value,t_bids.id)` from `t_system_options` `options` where (`options`.`_key` = 'loan_number')) AS `bid_no`,`t_bids`.`audit_time` AS `audit_time`,`t_user_details_credit_score`.`score` AS `score`,`t_bids`.`title` AS `title` from ((`t_user_details_credit_score` left join `t_users` on(((`t_users`.`id` = `t_user_details_credit_score`.`user_id`) and (`t_user_details_credit_score`.`operation` = 3)))) left join `t_bids` on((`t_user_details_credit_score`.`relation_id` = `t_bids`.`id`))) where 1=1 ";
	
	/**
	 * 用户投资信用积分
	 */
	public static final String V_USER_DETAIL_CREDIT_SCORE_INVEST = "`t_user_details_credit_score`.`id` AS `id`,`t_users`.`id` AS `user_id`,(select concat(`options`.`_value`,`t_bids`.`id`) AS `CONCAT(``options``._value,t_bids.id)` from `t_system_options` `options` where (`options`.`_key` = 'loan_number')) AS `bid_no`,`user`.`name` AS `name`,`t_bids`.`title` AS `title`,`t_user_details_credit_score`.`score` AS `score`,`t_invests`.`time` AS `invest_time` from ((((`t_user_details_credit_score` left join `t_users` on(((`t_users`.`id` = `t_user_details_credit_score`.`user_id`) and (`t_user_details_credit_score`.`operation` = 4)))) left join `t_invests` on((`t_user_details_credit_score`.`relation_id` = `t_invests`.`id`))) left join `t_bids` on((`t_invests`.`bid_id` = `t_bids`.`id`))) left join `t_users` `user` on((`t_bids`.`user_id` = `user`.`id`))) where 1=1 ";
	
	/**
	 * 用户资料审核信用积分
	 */
	public static final String V_USER_DETAIL_CREDIT_SCORE_AUDIT_ITEMS = "`t_user_details_credit_score`.`id` AS `id`,`t_users`.`id` AS `user_id`,`t_dict_audit_items`.`name` AS `audit_item_name`,`t_user_details_credit_score`.`score` AS `score`,`t_user_audit_items`.`audit_time` AS `audit_time`,`t_user_audit_items`.`suggestion` AS `suggestion` from (((`t_user_details_credit_score` join `t_users` on(((`t_users`.`id` = `t_user_details_credit_score`.`user_id`) and (`t_user_details_credit_score`.`operation` = 1)))) left join `t_user_audit_items` on(((`t_user_details_credit_score`.`relation_id` = `t_user_audit_items`.`id`) and (`t_user_audit_items`.`user_id` = `t_users`.`id`)))) left join `t_dict_audit_items` on((`t_user_audit_items`.`audit_item_id` = `t_dict_audit_items`.`id`))) where 1=1 ";
	
	/**
	 * CPS用户
	 */
	public static final String V_USER_CPS_USERS = "`t_users`.`id` AS `id`,`t_users`.`time` AS `time`,year(`t_users`.`time`) AS `year`,month(`t_users`.`time`) AS `month`,`t_users`.`name` AS `name`,`t_users`.`recommend_user_id` AS `recommend_user_id`,`t_users`.`is_active` AS `is_active`,(select ifnull(sum(`t_bids`.`amount`),0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_amount`,(select ifnull(sum(`t_invests`.`amount`),0) AS `invest_amount` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_amount` from `t_users` where `t_users`.`id` in (select `u`.`id` AS `id` from `t_users` `u` where (`u`.`recommend_user_id` > 0)) ";
	
	public static final String V_USER_CPS_USERS_COUNT = " select count(1) from `t_users` where `t_users`.`id` in (select `u`.`id` AS `id` from `t_users` `u` where (`u`.`recommend_user_id` > 0)) ";
	
	/**
	 * CPS用户详情
	 */
	public static final String V_USER_CPS_USER_INFO = "`t_users`.`id` AS `id`,`t_users`.`name` AS `name`,`t_users`.`time` AS `time`,timestampdiff(DAY,`t_users`.`time`,now()) AS `register_length`,(SELECT t_credit_levels.image_filename AS credit_level_image_filename FROM t_credit_levels WHERE (t_credit_levels.id = t_users.credit_level_id)) AS credit_level_image_filename,(select count(`user`.`id`) AS `COUNT(user.id)` from `t_users` `user` where (`user`.`recommend_user_id` = `t_users`.`id`)) AS `recommend_count`,(select count(`user`.`id`) AS `COUNT(user.id)` from `t_users` `user` where ((`user`.`recommend_user_id` = `t_users`.`id`) and (`user`.`is_active` = 1))) AS `recharge_count`,round(((select count(`user`.`id`) AS `COUNT(user.id)` from `t_users` `user` where ((`user`.`recommend_user_id` = `t_users`.`id`) and (`user`.`is_active` = 1))) DIV (select count(`user`.`id`) AS `COUNT(user.id)` from `t_users` `user` where (`user`.`recommend_user_id` = `t_users`.`id`))),4) AS `active_rate`,(select ifnull(sum(`t_bids`.`amount`),0) AS `bid_amount` from `t_bids` where (`t_bids`.`user_id` in (select distinct `user`.`id` AS `id` from `t_users` `user` where `user`.`recommend_user_id` in (select distinct `users`.`id` AS `id` from `t_users` `users` where (`users`.`recommend_user_id` = `t_users`.`id`))) and (`t_bids`.`status` in (4,5)))) AS `bid_amount`,(select ifnull(sum(`t_invests`.`amount`),0) AS `IFNULL(sum(t_invests.amount),0)` from (`t_invests` join `t_bids`) where ((`t_invests`.`bid_id` = `t_bids`.`id`) and (`t_bids`.`status` in (3,4,5)) and `t_invests`.`user_id` in (select distinct `user`.`id` AS `id` from `t_users` `user` where (`user`.`recommend_user_id` = `t_users`.`id`)))) AS `invest_amount`,(select ifnull(sum(`ucp`.`cps_reward`),0) AS `cps_reward` from `t_user_cps_income` `ucp` where (`ucp`.`user_id` = `t_users`.`id`)) AS `commission_amount` from `t_users` where `t_users`.`id` in (select distinct `t_users`.`recommend_user_id` AS `recommend_user_id` from `t_users` where (`t_users`.`recommend_user_id` > 0)) ";
	
	public static final String V_USER_CPS_USER_INFO_COUNT = " select count(1)  from `t_users` where `t_users`.`id` in (select distinct `t_users`.`recommend_user_id` AS `recommend_user_id` from `t_users` where (`t_users`.`recommend_user_id` > 0)) ";
	
	/**
	 * CPS用户统计
	 */
	public static final String V_USER_CPS_USER_COUNT = "`t_users`.`id` AS `id`,(select count(0) AS `count(*)` from `t_users` `u` where (`u`.`recommend_user_id` = `t_users`.`id`)) AS `cps_count`,(select count(0) AS `count(*)` from `t_users` `u` where ((`u`.`recommend_user_id` = `t_users`.`id`) and (`u`.`is_active` = 1))) AS `active_count`,(select count(0) AS `count(*)` from `t_users` `u` where ((`u`.`recommend_user_id` = `t_users`.`id`) and (`u`.`is_active` = 0))) AS `unactive_count`,(select ifnull(sum(`t_user_details`.`amount`),0) AS `commission_amount` from `t_user_details` where (`t_user_details`.`id` in (select `u`.`id` AS `id` from `t_users` `u` where (`u`.`recommend_user_id` = `t_users`.`id`)) and (`t_user_details`.`operation` in (51,52)))) AS `commission_amount` from `t_users` where 1=1 ";
	
	/**
	 * CPS用户信息
	 */
	public static final String V_USER_CPS_INFO = "`t_users`.`id` AS `id`,`t_users`.`name` AS `name`,`t_users`.`time` AS `register_time`,`t_users`.`credit_score` AS `credit_score`,`t_users`.`email` AS `email`,`t_users`.`mobile` AS `mobile`,`t_users`.`mobile1` AS `mobile1`,`t_users`.`mobile2` AS `mobile2`,`t_users`.`is_allow_login` AS `is_allow_login`,`t_users`.`vip_status` AS `vip_status`,`t_users`.`is_blacklist` AS `is_blacklist`,`t_users`.`recommend_user_id` AS `recommend_user_id`,(SELECT t_credit_levels.image_filename AS credit_level_image_filename FROM t_credit_levels WHERE (t_users.credit_level_id = t_credit_levels.id)) AS credit_level_image_filename,((`t_users`.`balance` + `t_users`.`freeze`) + `t_users`.`balance2`) AS `user_amount`,(select `u`.`name` AS `name` from `t_users` `u` where (`u`.`id` = `t_users`.`recommend_user_id`)) AS `recommend_user_name`,(select ifnull(sum(`t_user_details`.`amount`),0) AS `recharge_amount` from `t_user_details` where ((`t_user_details`.`user_id` = `t_users`.`id`) and (`t_user_details`.`operation` in (1,2,3)))) AS `recharge_amount`,(select ifnull(sum(`t_invests`.`amount`),0) AS `invest_amount` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_amount`,(select ifnull(sum(`t_bids`.`amount`),0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_amount`,(select ifnull(sum(`t_user_details`.`amount`),0) AS `commission_amount` from `t_user_details` where ((`t_user_details`.`id` = `t_users`.`id`) and (`t_user_details`.`operation` in (51,52)))) AS `commission_amount` from `t_users` where `t_users`.`recommend_user_id` > 0 ";
	
	/**
	 * CPS用户详情
	 */
	public static final String V_USER_CPS_DETAIL = "`user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`time` AS `time`,`user`.`recommend_user_id` AS `recommend_user_id`,timestampdiff(DAY,`user`.`time`,now()) AS `register_length`,(SELECT t_credit_levels.image_filename AS credit_level_image_filename FROM t_credit_levels WHERE (t_credit_levels.id = `user`.credit_level_id)) AS credit_level_image_filename,(select ifnull(sum(`detail`.`amount`),0) AS `IFNULL(sum(detail.amount),0)` from `t_user_details` `detail` where ((`detail`.`user_id` = `user`.`id`) and (`detail`.`operation` = 2))) AS `recharge_amount`,(select ifnull(sum(`t_bids`.`amount`),0) AS `bid_amount` from `t_bids` where ((`t_bids`.`user_id` = `user`.`id`) and (`t_bids`.`status` in (4,5)))) AS `bid_amount`,(select ifnull(sum(`t_invests`.`amount`),0) AS `invest_amount` from `t_invests` where (`t_invests`.`user_id` = `user`.`id`)) AS `invest_amount`,(select ifnull(sum(`detail`.`amount`),0) AS `IFNULL(sum(detail.amount),0)` from `t_user_details` `detail` where ((`detail`.`user_id` = `user`.`id`) and (`detail`.`operation` in (21,22)))) AS `repayment_amount` from `t_users` `user` where `user`.`recommend_user_id` > 0 ";
	
	/**
	 * 混合用户信息
	 */
	public static final String V_USER_COMPLEX_INFO = "`t_users`.`id` AS `id`,`f_credit_levels`(`t_users`.`id`) AS `num`,`t_users`.`name` AS `name`,`t_users`.`time` AS `register_time`,`t_users`.`credit_score` AS `credit_score`,`t_users`.`email` AS `email`,`t_users`.`mobile` AS `mobile`,`t_users`.`mobile1` AS `mobile1`,`t_users`.`mobile2` AS `mobile2`,`t_users`.`is_allow_login` AS `is_allow_login`,t_credit_levels.image_filename AS credit_level_image_filename,t_credit_levels.order_sort AS order_sort,((`t_users`.`balance` + `t_users`.`freeze`) + `t_users`.`balance2`) AS `user_amount`,(select ifnull(sum(`t_user_details`.`amount`),0) AS `recharge_amount` from `t_user_details` where ((`t_user_details`.`user_id` = `t_users`.`id`) and (`t_user_details`.`operation` in (1,2,3)))) AS `recharge_amount`,(select count(`t_invests`.`bid_id`) AS `invest_count` from `t_invests` where ((`t_invests`.`user_id` = `t_users`.`id`) and (`t_invests`.`transfers_id` = 0))) AS `invest_count`,(select ifnull(sum(`t_invests`.`amount`),0) AS `invest_amount` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_amount`,(select count(0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_count`,(select ifnull(sum(`t_bids`.`amount`),0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_amount`,(select count(0) AS `bid_loaning_count` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` = 2))) AS `bid_loaning_count`,(select ifnull(sum(((`t_bills`.`repayment_corpus` + `t_bills`.`real_repayment_interest`) + `t_bills`.`overdue_fine`)),0) AS `repayment_amount` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` in (-(1),-(2))))) AS `repayment_amount` from `t_users` LEFT JOIN t_credit_levels ON t_users.credit_level_id = t_credit_levels.id where `t_users`.`master_identity` = 3 ";
	
	/**
	 * 用户银行卡详情
	 */
	public static final String V_USER_BLACKLIST_INFO = "`t_users`.`id` AS `id`,`f_credit_levels`(`t_users`.`id`) AS `num`,`t_users`.`name` AS `name`,`t_users`.`time` AS `register_time`,`t_users`.`credit_score` AS `credit_score`,`t_users`.`is_allow_login` AS `is_allow_login`,`t_users`.`is_blacklist` AS `is_blacklist`,`t_users`.`joined_time` AS `join_time`,t_credit_levels.image_filename AS credit_level_image_filename,t_credit_levels.order_sort AS order_sort,((`t_users`.`balance` + `t_users`.`freeze`) + `t_users`.`balance2`) AS `user_amount`,(select ifnull(sum(`t_user_details`.`amount`),0) AS `recharge_amount` from `t_user_details` where ((`t_user_details`.`user_id` = `t_users`.`id`) and (`t_user_details`.`operation` in (1,2,3)))) AS `recharge_amount`,(select count(`t_invests`.`bid_id`) AS `invest_count` from `t_invests` where ((`t_invests`.`user_id` = `t_users`.`id`) and (`t_invests`.`transfers_id` = 0))) AS `invest_count`,(select ifnull(sum(`t_invests`.`amount`),0) AS `invest_amount` from `t_invests` where (`t_invests`.`user_id` = `t_users`.`id`)) AS `invest_amount`,(select count(0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_count`,(select ifnull(sum(`t_bids`.`amount`),0) AS `bid_count` from `t_bids` where (`t_bids`.`user_id` = `t_users`.`id`)) AS `bid_amount`,(select count(0) AS `overdue_bill_count` from (`t_bills` join `t_bids` on((`t_bills`.`bid_id` = `t_bids`.`id`))) where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` < 0))) AS `overdue_bill_count`,(select count(0) AS `count(*)` from `t_user_report_users` where (`t_user_report_users`.`reported_user_id` = `t_users`.`id`)) AS `reported_count` from `t_users` LEFT JOIN t_credit_levels ON ((t_users.credit_level_id = t_credit_levels.id)) where `t_users`.`is_blacklist` = 1 ";
	
	/**
	 * 用户银行列表
	 */
	public static final String V_USER_BLACKLIST = "`t_user_blacklist`.`id` AS `id`,`t_user_blacklist`.`user_id` AS `user_id`,`t_user_blacklist`.`time` AS `time`,`t_user_blacklist`.`bid_id` AS `bid_id`,`t_user_blacklist`.`black_user_id` AS `black_user_id`,`t_user_blacklist`.`reason` AS `reason`,`t_users`.`name` AS `blacklist_name` from `t_user_blacklist` join `t_users` on`t_user_blacklist`.`black_user_id` = `t_users`.`id` where 1=1 ";
	
	/**
	 * 用户审核的资料
	 */
	public static final String V_USER_AUDIT_ITEMS = "";
	
	/**
	 * 用户审核的资料状态
	 */
	//public static final String V_USER_AUDIT_ITEM_STATS = "`u`.`id` AS `id`,`u`.`name` AS `user_name`,`u`.`email` AS `email`,`u`.`mobile` AS `mobile`,`u`.`time` AS `time`,(select count(`udi`.`id`) AS `sum_count` from `t_user_audit_items` `udi` where (`udi`.`user_id` = `u`.`id`)) AS `sum_count`,(select count(`udi`.`id`) AS `audited_count` from `t_user_audit_items` `udi` where ((`udi`.`user_id` = `u`.`id`) and (`udi`.`status` = 2))) AS `audited_count`,(select count(`udi`.`id`) AS `not_pass_count` from `t_user_audit_items` `udi` where ((`udi`.`user_id` = `u`.`id`) and (`udi`.`status` = -(1)))) AS `not_pass_count`,(select count(`udi`.`id`) AS `auditing_count` from `t_user_audit_items` `udi` where ((`udi`.`user_id` = `u`.`id`) and (`udi`.`status` = 1))) AS `auditing_count`,(select count(`b`.`id`) AS `bid_success_count` from `t_bids` `b` where ((`b`.`user_id` = `u`.`id`) and (`b`.`status` in (4,5)))) AS `bid_success_count`,(select count(`b`.`id`) AS `bid_loaning_count` from `t_bids` `b` where ((`b`.`user_id` = `u`.`id`) and (`b`.`status` in (1,2)))) AS `bid_loaning_count`,(select count(`i`.`id`) AS `invest_count` from `t_invests` `i` where (`i`.`user_id` = `u`.`id`)) AS `invest_count` from `t_users` `u` where 1=1 ";
	
	/**
	 * 关注的用户信息
	 */
	public static final String V_USER_ATTENTION_INFO = "`t_user_attention_users`.`id` AS `id`,`t_user_attention_users`.`user_id` AS `user_id`,`t_user_attention_users`.`time` AS `time`,`t_user_attention_users`.`attention_user_id` AS `attention_user_id`,`t_user_attention_users`.`note_name` AS `note_name`,`t_users`.`name` AS `attention_user_name`,`t_users`.`photo` AS `attention_user_photo` from `t_user_attention_users` join `t_users` on  `t_user_attention_users`.`attention_user_id` = `t_users`.`id` where 1=1 ";
	
	/**
	 * 用户账户统计
	 */
	public static final String V_USER_ACCOUNT_STATISTICS = "`a`.`id` AS `id`,(select count(`b`.`id`) AS `auditing_count` from `t_bids` `b` where ((`b`.`user_id` = `a`.`id`) and (`b`.`status` = 0))) AS `auditing_count`,(select count(`b`.`id`) AS `repaymenting_count` from `t_bids` `b` where ((`b`.`user_id` = `a`.`id`) and (`b`.`status` = 4))) AS `repaymenting_count`,(select count(`b`.`id`) AS `count(``b``.``id``)` from (`t_bills` `b` join `t_bids` `d` on((`b`.`bid_id` = `d`.`id`))) where ((`b`.`repayment_time` between now() and (now() + interval 30 day)) and (`a`.`id` = `d`.`user_id`) and (`b`.`status` = -(1)))) AS `untreated_bills_count`,(select count(`b`.`id`) AS `COUNT(b.id)` from `t_bill_invests` `b` where ((`b`.`receive_time` between now() and (now() + interval 30 day)) and (`b`.`user_id` = `a`.`id`))) AS `untreated_invest_bills_count`,(select count(`b`.`id`) AS `count(b.id)` from (`t_bills` `b` join `t_bids` `c`) where ((`b`.`overdue_mark` in (-(1),-(2),-(3))) and (`b`.`bid_id` = `c`.`id`) and (`c`.`user_id` = `a`.`id`))) AS `overdue_bills_count`,(select count(`b`.`id`) AS `COUNT(b.id)` from (`t_bids` `b` join `t_invests` `c`) where ((`b`.`id` = `c`.`bid_id`) and (`a`.`id` = `c`.`user_id`) and (`c`.`transfer_status` in (0,1)) and (`b`.`status` = 4))) AS `receivable_invest_bids_count`,(select count(`b`.`id`) AS `COUNT(b.id)` from `t_user_audit_items` `b` where ((`b`.`user_id` = `a`.`id`) and (`b`.`status` = 0))) AS `lack_audit_item_count` from `t_users` `a` where 1=1 ";
	
	/**
	 * 用户账户
	 */
	public static final String V_USER_ACCOUNT_INFO = "`t_users`.`id` AS `id`,(`t_users`.`balance` + `t_users`.`freeze`) AS `user_account`,`t_users`.`balance2` AS `user_account2`,`t_users`.`freeze` AS `freeze`,ifnull((select sum(`t_invests`.`amount`) AS `sum(t_invests.amount)` from `t_invests` where ((`t_invests`.`user_id` = `t_users`.`id`) and (`t_invests`.`transfer_status` <> -(1)))),0) AS `invest_amount`,ifnull((select count(distinct `t_invests`.`bid_id`) AS `count(DISTINCT ``t_invests``.bid_id)` from `t_invests` where ((`t_invests`.`user_id` = `t_users`.`id`) and (`t_invests`.`transfer_status` <> -(1)))),0) AS `invest_count`,ifnull((select sum(`t_bids`.`amount`) AS `bid_amount` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` in (4,5)))),0) AS `bid_amount`,ifnull((select count(`t_bids`.`id`) AS `bid_count` from `t_bids` where ((`t_bids`.`user_id` = `t_users`.`id`) and (`t_bids`.`status` >= 0))),0) AS `bid_count`,ifnull((select sum((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`)) AS `receive_amount` from `t_bill_invests` where ((`t_bill_invests`.`user_id` = `t_users`.`id`) and (`t_bill_invests`.`status` in (-(1),-(2))))),0) AS `receive_amount`,ifnull((select sum((`t_bills`.`repayment_corpus` + `t_bills`.`repayment_interest`)) AS `repayment_amount` from (`t_bills` join `t_bids`) where ((`t_bills`.`bid_id` = `t_bids`.`id`) and (`t_bids`.`user_id` = `t_users`.`id`) and (`t_bills`.`status` in (-(1),-(2))))),0) AS `repayment_amount` from `t_users` where 1=1 ";
	
	/**
	 * 管理员
	 */
	public static final String V_SUPERVISORS = "`t_supervisors`.`id` AS `id`,`t_supervisors`.`time` AS `time`,`t_supervisors`.`name` AS `name`,`t_supervisors`.`reality_name` AS `reality_name`,`t_supervisors`.`password` AS `password`,`t_supervisors`.`password_continuous_errors` AS `password_continuous_errors`,`t_supervisors`.`is_password_error_locked` AS `is_password_error_locked`,`t_supervisors`.`password_error_locked_time` AS `password_error_locked_time`,`t_supervisors`.`is_allow_login` AS `is_allow_login`,`t_supervisors`.`login_count` AS `login_count`,`t_supervisors`.`last_login_time` AS `last_login_time`,`t_supervisors`.`last_login_ip` AS `last_login_ip`,`t_supervisors`.`last_logout_time` AS `last_logout_time`,`t_supervisors`.`email` AS `email`,`t_supervisors`.`telephone` AS `telephone`,`t_supervisors`.`mobile1` AS `mobile1`,`t_supervisors`.`mobile2` AS `mobile2`,`t_supervisors`.`office_telephone` AS `office_telephone`,`t_supervisors`.`fax_number` AS `fax_number`,`t_supervisors`.`sex` AS `sex`,`t_supervisors`.`birthday` AS `birthday`,`t_supervisors`.`level` AS `level`,`t_supervisors`.`is_erased` AS `is_erased`,`t_supervisors`.`creater_id` AS `creater_id`,`t_supervisors`.`ukey` AS `ukey`,`t_supervisors`.`is_customer` AS `is_customer`,`t_supervisors`.`customer_num` AS `customer_num`,(select count(0) AS `count(*)` from `t_users` where (`t_users`.`assigned_to_supervisor_id` = `t_supervisors`.`id`)) AS `customer_user_count`,(select cast(group_concat(`rg`.`name` separator ',') as char charset utf8) AS `cast(group_concat(``name``) as char)` from (`t_right_groups` `rg` left join `t_right_groups_of_supervisor` `rgos` on((`rg`.`id` = `rgos`.`group_id`))) where (`rgos`.`supervisor_id` = `t_supervisors`.`id`)) AS `right_group` from `t_supervisors` where (isnull(`t_supervisors`.`is_erased`) or (`t_supervisors`.`is_erased` = 0)) ";
	
	/**
	 * 管理员事件
	 */
	public static final String  V_SUPERVISOR_EVENTS = "`t_supervisor_events`.`id` AS `id`,`t_supervisor_events`.`supervisor_id` AS `supervisor_id`,`t_supervisor_events`.`time` AS `time`,`t_supervisor_events`.`ip` AS `ip`,`t_supervisor_events`.`type_id` AS `type_id`,`t_supervisor_events`.`descrption` AS `descrption`,concat(`t_supervisors`.`reality_name`,`t_supervisor_events`.`descrption`) AS `content`,`t_supervisors`.`name` AS `supervisor_name`,`t_supervisors`.`level` AS `supervisor_level`,`t_supervisor_event_types`.`name` AS `type_name`,`t_supervisor_event_types`.`description` AS `type_description`,`t_supervisors`.`ukey` AS `ukey` from ((`t_supervisor_events` left join `t_supervisor_event_types` on((`t_supervisor_events`.`type_id` = `t_supervisor_event_types`.`id`))) left join `t_supervisors` on((`t_supervisor_events`.`supervisor_id` = `t_supervisors`.`id`))) where 1=1 ";
	
	/**
	 * 权限组
	 */
	public static final String V_RIGHT_GROUPS = "`rg`.`id` AS `id`,`rg`.`name` AS `name`,`rg`.`description` AS `description`,`rg`.`right_modules` AS `right_modules`,(select count(`gos`.`id`) AS `count(gos.id)` from `t_right_groups_of_supervisor` `gos` where (`gos`.`group_id` = `rg`.`id`)) AS `supervisor_count` from `t_right_groups` `rg` where 1=1 ";
	
	/**
	 * 收款中的理财标的
	 */
	public static final String V_RECEIVING_INVEST_BIDS = "`t_invests`.`id` AS `id`,`t_invests`.`user_id` AS `user_id`,`t_invests`.`bid_id` AS `bid_id`,`t_invests`.`transfer_status` AS `transfer_status`,`t_invests`.`transfers_id` AS `transfers_id`,`t_bids`.`title` AS `title`,`t_bids`.`amount` AS `bid_amount`,`t_bids`.`apr` AS `apr`,`t_invests`.`amount` AS `invest_amount`,`t_bids`.`period_unit` AS `period_unit`,`t_bids`.`is_sec_bid` AS `is_sec_bid`,`t_bids`.`is_agency` AS `is_agency`,`t_users`.`name` AS `name`,concat((select `t_system_options`.`_value` AS `_value` from `t_system_options` where (`t_system_options`.`_key` = 'loan_number')),(`t_bids`.`id` + '')) AS `no`,ifnull((select ifnull(sum(((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`)),0) AS `has_received_amount` from `t_bill_invests` where ((`t_invests`.`id` = `t_bill_invests`.`invest_id`) and (`t_bill_invests`.`status` in (-(3),-(4),0)))),0) AS `has_received_amount`,(select ifnull(sum(((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`)),0) AS `receiving_amount` from `t_bill_invests` where (`t_invests`.`id` = `t_bill_invests`.`invest_id`)) AS `receiving_amount`,(select count(`t_bill_invests`.`id`) AS `dd` from `t_bill_invests` where ((`t_invests`.`id` = `t_bill_invests`.`invest_id`) and (`t_bill_invests`.`status` = -(2)))) AS `overdue_payback_period`,(select count(`t_bill_invests`.`id`) AS `ff` from `t_bill_invests` where ((`t_invests`.`id` = `t_bill_invests`.`invest_id`) and (`t_bill_invests`.`status` in (-(3),-(4),0)))) AS `has_payback_period`,(select count(`t_bill_invests`.`id`) AS `ff` from `t_bill_invests` where (`t_invests`.`id` = `t_bill_invests`.`invest_id`)) AS `period` from ((`t_invests` left join `t_bids` on((`t_bids`.`id` = `t_invests`.`bid_id`))) left join `t_users` on((`t_users`.`id` = `t_bids`.`user_id`))) where ((`t_bids`.`status` = 4) and (`t_invests`.`transfer_status` <> -(1))) ";
	
	/**
	 * 平台信息详情
	 */
	public static final String V_PLATFORM_DETAIL = "`detail`.`id` AS `id`,`detail`.`operation` AS `operation`,`detail`.`relation_id` AS `relation_id`,`detail`.from_pay_name AS `from_pay`,`detail`.to_receive_name AS `to_receive`,`detail`.`payment` AS `payment`,`detail`.`type` AS `type`,`detail`.`amount` AS `amount`,`detail`.`balance` AS `balance`,`detail`.`summary` AS `summary`,`detail`.`time` AS `time`,`type`.`name` AS `name` from ( `t_platform_details` `detail` JOIN `t_platform_detail_types` `type` ON ((( `detail`.`operation` = `type`.`id` ) AND ( `detail`.`operation` = `type`.`id` )))) where 1 = 1 ";
	
	/**
	 * 新闻类型
	 */
	public static final String V_NEWS_TYPES = "`type`.`id` AS `id`,`type`.`name` AS `name`,`type`.`parent_id` AS `parent_id`,`type`.`status` AS `status`,`type`.`_order` AS `_order`,(select count(0) AS `count(*)` from `t_content_news` `news` where (`news`.`type_id` = `type`.`id`)) AS `counts` from `t_content_news_types` `type` where 1=1 ";
	
	/**
	 * 外部消息
	 */
	public static final String V_MESSAGES_USER_OUTBOX = "`m`.`id` AS `id`,`m`.`sender_user_id` AS `user_id`,if((`m`.`receiver_supervisor_id` > 0),'admin',if((`m`.`receiver_user_id` <> -(10)),(select `t_users`.`name` AS `name` from `t_users` where (`t_users`.`id` = `m`.`receiver_user_id`)),(select concat((select `u`.`name` AS `NAME` from `t_users` `u` where (`u`.`id` = `t_messages_receivers`.`user_id`)),'等',(select count(`t_messages_receivers`.`id`) AS `count(id)` from `t_messages_receivers` where (`t_messages_receivers`.`message_id` = `m`.`id`)),'人') AS `receiver_name` from `t_messages_receivers` where (`t_messages_receivers`.`message_id` = `m`.`id`)))) AS `receiver_name`,`m`.`title` AS `title`,`m`.`time` AS `time`,`m`.`content` AS `content` from `t_messages` `m` where ((`m`.`sender_user_id` > 0) and (`m`.`is_erased` = 0)) ";
	
	
	/**
	 * 内部消息
	 */
	public static final  String V_MESSAGES_USER_INBOX = "";

	/**
	 * 系统消息
	 */
	public static final  String V_MESSAGES_SYSTEM = "";
	
	/**
	 * 管理员外部消息
	 */
	public static final  String V_MESSAGES_SUPERVISOR_OUTBOX = "";
	
	public static final  String V_MESSAGES_SUPERVISOR_INBOX = "";
	
	public static final  String V_MESSAGES_SUPERVISOR_DUSTBIN = "";
	
	/**
	 * 投资记录
	 */
	public static final  String V_INVEST_RECORDS = "`t_invests`.`id` AS `id`,`t_bids`.`title` AS `title`,`t_invests`.`time` AS `time`,`t_invests`.`amount` AS `invest_amount`,`t_bids`.`amount` AS `bid_amount`,`t_bids`.`apr` AS `apr`,`t_bids`.`status` AS `status`,`t_invests`.`transfer_status` AS `transfer_status`,`t_users`.`name` AS `name`,`t_invests`.`bid_id` AS `bid_id`,`t_invests`.`user_id` AS `user_id`,`bid_user`.`name` AS `bid_user_name`,concat(`t_system_options`.`_value`,(`t_invests`.`bid_id` + '')) AS `no`,(select count(`t_bid_questions`.`id`) AS `count(id)` from `t_bid_questions` where ((`t_bid_questions`.`bid_id` = `t_invests`.`bid_id`) and (`t_bid_questions`.`user_id` = `t_invests`.`user_id`))) AS `question_count`,(select count(`t_bid_answers`.`id`) AS `count(id)` from `t_bid_answers` where `t_bid_answers`.`bid_question_id` in (select `t_bid_questions`.`id` AS `id` from `t_bid_questions` where ((`t_bid_questions`.`bid_id` = `t_invests`.`bid_id`) and (`t_bid_questions`.`user_id` = `t_invests`.`user_id`)))) AS `answer_count` from ((((`t_invests` left join `t_bids` on((`t_bids`.`id` = `t_invests`.`bid_id`))) join `t_system_options`) left join `t_users` on((`t_users`.`id` = `t_invests`.`user_id`))) left join `t_users` `bid_user` on((`bid_user`.`id` = `t_bids`.`user_id`))) where (`t_system_options`.`_key` = 'loan_number') ";
	
	/**
	 * 前台用户关注的标的
	 */
	public static final String V_FRONT_USER_ATTENTION_BIDS = "`t_user_attention_bids`.`id` AS `id`,`t_credit_levels`.`name` AS `credit_name`,`t_credit_levels`.`image_filename` AS `credit_image_filename`,`t_products`.`name_image_filename` AS `product_filename`,`t_products`.`name` AS `product_name`,`t_bids`.`title` AS `title`,`t_bids`.`amount` AS `amount`,`t_bids`.`period` AS `period`,`t_bids`.`period_unit` AS `period_unit`,`t_bids`.`apr` AS `apr`,`t_bids`.`is_hot` AS `is_hot`,`t_bids`.`is_agency` AS `is_agency`,`t_agencies`.`name` AS `agency_name`,`t_bids`.`has_invested_amount` AS `has_invested_amount`,`t_bids`.`image_filename` AS `bid_image_filename`,`t_products`.`small_image_filename` AS `small_image_filename`,`t_bids`.`loan_schedule` AS `loan_schedule`,`t_bids`.`bonus_type` AS `bonus_type`,`t_bids`.`bonus` AS `bonus`,(select `t_bills`.`repayment_time` AS `repayment_time` from `t_bills` where ((`t_bids`.`id` = `t_bills`.`bid_id`) and (`t_bills`.`status` = -(1))) group by `t_bills`.`bid_id` order by `t_bills`.`bid_id`) AS `repayment_time`,concat((select `t_system_options`.`_value` AS `_value` from `t_system_options` where (`t_system_options`.`_key` = 'loan_number')),(`t_bids`.`id` + '')) AS `no`,`t_bids`.`award_scale` AS `award_scale`,`t_dict_bid_repayment_types`.`name` AS `repay_name`,`t_user_attention_bids`.`user_id` AS `user_id`,`t_user_attention_bids`.`bid_id` AS `bid_id`,`t_bids`.`is_show_agency_name` AS `is_show_agency_name`,`t_products`.`id` AS `product_id`,`t_users`.`credit_level_id` AS `credit_level_id`,`t_bids`.`time` AS `time` from (`t_user_attention_bids` left join (((((`t_bids` left join `t_products` on((`t_products`.`id` = `t_bids`.`product_id`))) left join `t_users` on((`t_users`.`id` = `t_bids`.`user_id`))) left join `t_credit_levels` on((`t_credit_levels`.`id` = `t_users`.`credit_level_id`))) left join `t_agencies` on((`t_agencies`.`id` = `t_bids`.`agency_id`))) left join `t_dict_bid_repayment_types` on((`t_dict_bid_repayment_types`.`id` = `t_products`.`repayment_type_id`))) on((`t_bids`.`id` = `t_user_attention_bids`.`bid_id`))) where (`t_bids`.`status` in (1,2,3,4,5)) ";
	
	/**
	 * 前台所有的债权转让
	 */
	public static final String V_FRONT_ALL_DEBTS = "`t_invest_transfers`.`id` AS `id`,`t_invest_transfers`.`time` AS `time`,`t_invest_transfers`.`title` AS `title`,`t_invest_transfers`.`transer_reason` AS `transfer_reason`,`t_invest_transfers`.`debt_amount` AS `debt_amount`,`t_invest_transfers`.`status` AS `status`,`t_invest_transfers`.`end_time` AS `end_time`,`t_bids`.`amount` AS `bid_amount`,`t_bids`.`apr` AS `apr`,`t_bids`.`user_id` AS `user_id`,`t_bids`.`image_filename` AS `bid_image_filename`,`t_bids`.`product_id` AS `product_id`,`t_products`.`name` AS `product_name`,`t_products`.`name_image_filename` AS `product_image_filename`,`t_products`.`small_image_filename` AS `small_image_filename`,`t_users`.`credit_level_id` AS `credit_level_id`,(select max(`t_invest_transfer_details`.`offer_price`) AS `max_price` from `t_invest_transfer_details` where (`t_invest_transfer_details`.`transfer_id` = `t_invest_transfers`.`id`)) AS `max_price`,(select `t_bills`.`repayment_time` AS `repayment_time` from `t_bills` where ((`t_bills`.`bid_id` = `t_bids`.`id`) and (`t_bills`.`status` = -(1))) group by `t_bills`.`bid_id` order by `t_bills`.`repayment_time`) AS `repayment_time`,`t_invest_transfers`.`is_quality_debt` AS `is_quality_debt`,`t_invest_transfers`.`transfer_price` AS `transfer_price`,`t_credit_levels`.`image_filename` AS `credit_image_filename` from (((((`t_invest_transfers` left join `t_invests` on((`t_invests`.`id` = `t_invest_transfers`.`invest_id`))) left join `t_bids` on((`t_bids`.`id` = `t_invests`.`bid_id`))) left join `t_products` on((`t_products`.`id` = `t_bids`.`product_id`))) left join `t_users` on((`t_users`.`id` = `t_bids`.`user_id`))) left join `t_credit_levels` on((`t_credit_levels`.`id` = `t_users`.`credit_level_id`))) where (`t_invest_transfers`.`status` in (1,2,3,4)) ";
	
	/**
	 * 前台所有的标的
	 */
	public static final String V_FRONT_ALL_BIDS = "`t_bids`.`id` AS `id`,`t_products`.`name_image_filename` AS `product_filename`,`t_products`.`name` AS `product_name`,`t_bids`.`show_type` AS `show_type`,`t_bids`.`title` AS `title`,`t_bids`.`amount` AS `amount`,`t_bids`.`status` AS `status`,`t_bids`.`user_id` AS `user_id`,`t_bids`.`period` AS `period`,`t_bids`.`apr` AS `apr`,`t_bids`.`is_hot` AS `is_hot`,`t_bids`.`period_unit` AS `period_unit`,`t_bids`.`is_agency` AS `is_agency`,`t_agencies`.`name` AS `agency_name`,`t_bids`.`has_invested_amount` AS `has_invested_amount`,`t_bids`.`image_filename` AS `bid_image_filename`,`t_products`.`small_image_filename` AS `small_image_filename`,`t_bids`.`loan_schedule` AS `loan_schedule`,`t_bids`.`bonus_type` AS `bonus_type`,`t_bids`.`bonus` AS `bonus`,t_bids.repayment_time AS repayment_time,concat (( SELECT `t_system_options`.`_value` AS `_value` FROM `t_system_options` WHERE (`t_system_options`.`_key` = 'loan_number')),(`t_bids`.`id` + '')) AS `no`,`t_bids`.`award_scale` AS `award_scale`,`t_bids`.`repayment_type_id` AS `repayment_type_id`,`t_dict_bid_repayment_types`.`name` AS `repay_name`,`t_bids`.`is_show_agency_name` AS `is_show_agency_name`,`t_products`.`id` AS `product_id`,`f_credit_levels` (`t_bids`.`user_id`) AS `num`,`t_bids`.`time` AS `time` from `t_bids` LEFT JOIN `t_products` ON `t_products`.`id` = `t_bids`.`product_id` LEFT JOIN `t_agencies` ON `t_agencies`.`id` = `t_bids`.`agency_id` LEFT JOIN `t_dict_bid_repayment_types` ON `t_dict_bid_repayment_types`.`id` = `t_bids`.`repayment_type_id` where `t_bids`.`status` IN (1, 2, 3, 4, 5, 14)";
	
	public static final String V_FRONT_ALL_BIDS_CREDIT = "`t_bids`.`is_quality` AS `is_quality`,`t_bids`.`min_invest_amount` AS `min_invest_amount`,`t_bids`.`feeType` AS `feeType`,`t_bids`.`is_new` AS `is_new`,`t_bids`.`id` AS `id`, `t_bids`.`min_invest_amount` AS `min_invest_amount`,`t_credit_levels`.`name` AS `credit_name`,  `t_credit_levels`.`image_filename` AS `credit_image_filename`,  `t_products`.`name_image_filename` AS `product_filename`,  `t_products`.`name` AS `product_name`,  `t_bids`.`show_type` AS `show_type`,  `t_bids`.`title` AS `title`,  `t_bids`.`amount` AS `amount`,  `t_bids`.`status` AS `status`,  `t_bids`.`user_id` AS `user_id`,  `t_bids`.`period` AS `period`,  `t_bids`.`apr` AS `apr`,  `t_bids`.`is_hot` AS `is_hot`,  `t_bids`.`period_unit` AS `period_unit`,  `t_bids`.`is_agency` AS `is_agency`,  `t_agencies`.`name` AS `agency_name`,  `t_bids`.`has_invested_amount` AS `has_invested_amount`,  `t_bids`.`image_filename` AS `bid_image_filename`,  `t_products`.`small_image_filename` AS `small_image_filename`,  `t_bids`.`loan_schedule` AS `loan_schedule`,  `t_bids`.`bonus_type` AS `bonus_type`,  `t_bids`.`bonus` AS `bonus`,  (SELECT  `t_bills`.`repayment_time` AS `repayment_time`  from `t_bills`  where ((`t_bids`.`id` = `t_bills`.`bid_id`)  AND (`t_bills`.`status` = - (1)))  GROUP BY `t_bills`.`bid_id`  order by `t_bills`.`bid_id`) AS `repayment_time`,  CONCAT((SELECT `t_system_options`.`_value` AS `_value` from `t_system_options` where (`t_system_options`.`_key` = 'loan_number')),(`t_bids`.`id` + '')) AS `no`,  `t_bids`.`award_scale` AS `award_scale`,  `t_bids`.`repayment_type_id` AS `repayment_type_id`,  `t_dict_bid_repayment_types`.`name` AS `repay_name`,  `t_bids`.`is_show_agency_name` AS `is_show_agency_name`,  `t_products`.`id` AS `product_id`,  `f_credit_levels`( `t_users`.`id`) AS `num`,  `t_users`.`credit_level_id` AS `credit_level_id`,  `t_bids`.`time` AS `time` from (((((`t_bids`  LEFT JOIN `t_products`  ON ((`t_products`.`id` = `t_bids`.`product_id`)))  LEFT JOIN `t_users`  ON ((`t_users`.`id` = `t_bids`.`user_id`)))  LEFT JOIN `t_credit_levels`  ON ((`t_credit_levels`.`id` = `t_users`.`credit_level_id`)))  LEFT JOIN `t_agencies`  ON ((`t_agencies`.`id` = `t_bids`.`agency_id`)))  LEFT JOIN `t_dict_bid_repayment_types`  ON ((`t_dict_bid_repayment_types`.`id` = `t_bids`.`repayment_type_id`))) where (`t_bids`.`status` IN(1,2,3,4,5,14)) ";

	public static final String V_FRONT_ALL_BIDS_LIST1 = "`t_bids`.`is_quality` AS `is_quality`,`t_bids`.`feeType` AS `feeType`,`t_bids`.`is_new` AS `is_new`,`t_bids`.`id` AS `id`, `t_bids`.`min_invest_amount` AS `min_invest_amount`,`t_credit_levels`.`name` AS `credit_name`,  `t_credit_levels`.`image_filename` AS `credit_image_filename`,  `t_products`.`name_image_filename` AS `product_filename`,  `t_products`.`name` AS `product_name`,  `t_bids`.`show_type` AS `show_type`,  `t_bids`.`title` AS `title`,  `t_bids`.`amount` AS `amount`,  `t_bids`.`status` AS `status`,  `t_bids`.`user_id` AS `user_id`,  `t_bids`.`period` AS `period`,  `t_bids`.`apr` AS `apr`,  `t_bids`.`is_hot` AS `is_hot`,  `t_bids`.`period_unit` AS `period_unit`,  `t_bids`.`is_agency` AS `is_agency`,  `t_agencies`.`name` AS `agency_name`,  `t_bids`.`has_invested_amount` AS `has_invested_amount`,  `t_bids`.`image_filename` AS `bid_image_filename`,  `t_products`.`small_image_filename` AS `small_image_filename`,  `t_bids`.`loan_schedule` AS `loan_schedule`,  `t_bids`.`bonus_type` AS `bonus_type`,  `t_bids`.`bonus` AS `bonus`,  (SELECT  `t_bills`.`repayment_time` AS `repayment_time`  from `t_bills`  where ((`t_bids`.`id` = `t_bills`.`bid_id`)  AND (`t_bills`.`status` = - (1)))  GROUP BY `t_bills`.`bid_id`  order by `t_bills`.`bid_id`) AS `repayment_time`,  CONCAT((SELECT `t_system_options`.`_value` AS `_value` from `t_system_options` where (`t_system_options`.`_key` = 'loan_number')),(`t_bids`.`id` + '')) AS `no`,  `t_bids`.`award_scale` AS `award_scale`,  `t_bids`.`repayment_type_id` AS `repayment_type_id`,  `t_dict_bid_repayment_types`.`name` AS `repay_name`,  `t_bids`.`is_show_agency_name` AS `is_show_agency_name`,  `t_products`.`id` AS `product_id`,  `f_credit_levels`( `t_users`.`id`) AS `num`,  `t_users`.`credit_level_id` AS `credit_level_id`,  `t_bids`.`time` AS `time` from (((((`t_bids`  LEFT JOIN `t_products`  ON ((`t_products`.`id` = `t_bids`.`product_id`)))  LEFT JOIN `t_users`  ON ((`t_users`.`id` = `t_bids`.`user_id`)))  LEFT JOIN `t_credit_levels`  ON ((`t_credit_levels`.`id` = `t_users`.`credit_level_id`)))  LEFT JOIN `t_agencies`  ON ((`t_agencies`.`id` = `t_bids`.`agency_id`)))  LEFT JOIN `t_dict_bid_repayment_types`  ON ((`t_dict_bid_repayment_types`.`id` = `t_bids`.`repayment_type_id`))) ";

	/**
	 * 前台所有标的增加起购金额字段
	 */
	public static final String V_FRONT_ALL_BIDS_ADDAMOUNT = "`t_bids`.`id` AS `id`,`t_bids`.`min_invest_amount` AS `min_invest_amount`,`t_bids`.`feeType` AS `feeType`,`t_bids`.`is_new` AS `is_new`,`t_products`.`name_image_filename` AS `product_filename`,`t_products`.`name` AS `product_name`,`t_bids`.`show_type` AS `show_type`,`t_bids`.`title` AS `title`,`t_bids`.`amount` AS `amount`,`t_bids`.`status` AS `status`,`t_bids`.`user_id` AS `user_id`,`t_bids`.`period` AS `period`,`t_bids`.`apr` AS `apr`,`t_bids`.`is_hot` AS `is_hot`,`t_bids`.`period_unit` AS `period_unit`,`t_bids`.`is_agency` AS `is_agency`,`t_agencies`.`name` AS `agency_name`,`t_bids`.`has_invested_amount` AS `has_invested_amount`,`t_bids`.`image_filename` AS `bid_image_filename`,`t_products`.`small_image_filename` AS `small_image_filename`,`t_bids`.`loan_schedule` AS `loan_schedule`,`t_bids`.`bonus_type` AS `bonus_type`,`t_bids`.`bonus` AS `bonus`,t_bids.repayment_time AS repayment_time,concat (( SELECT `t_system_options`.`_value` AS `_value` FROM `t_system_options` WHERE (`t_system_options`.`_key` = 'loan_number')),(`t_bids`.`id` + '')) AS `no`,`t_bids`.`award_scale` AS `award_scale`,`t_bids`.`repayment_type_id` AS `repayment_type_id`,`t_dict_bid_repayment_types`.`name` AS `repay_name`,`t_bids`.`is_show_agency_name` AS `is_show_agency_name`,`t_products`.`id` AS `product_id`,`f_credit_levels` (`t_bids`.`user_id`) AS `num`,`t_bids`.`time` AS `time` from `t_bids` LEFT JOIN `t_products` ON `t_products`.`id` = `t_bids`.`product_id` LEFT JOIN `t_agencies` ON `t_agencies`.`id` = `t_bids`.`agency_id` LEFT JOIN `t_dict_bid_repayment_types` ON `t_dict_bid_repayment_types`.`id` = `t_bids`.`repayment_type_id` where `t_bids`.`status` IN (1, 2, 3, 4, 5, 14)";
	/**
	 * 展示第一个标（预）
	 */
	public static final String V_FRONT_HOMEPAGE_SHOW_BID = "`t_bids`.`id` AS `id`,concat (( SELECT `t_system_options`.`_value` AS `_value` FROM `t_system_options` WHERE (`t_system_options`.`_key` = 'loan_number')),(`t_bids`.`id` + '')) AS `no`,`t_bids`.`min_invest_amount` AS `min_invest_amount`,`t_bids`.`period_unit` AS `period_unit`,`t_bids`.`title` AS `title`,`t_bids`.`amount` AS `amount`,`t_bids`.`period` AS `period`,`t_bids`.`apr` AS `apr`,`t_bids`.`has_invested_amount` AS `has_invested_amount`,`t_bids`.`status` AS `status` from `t_bids`  where `t_bids`.`status` IN (1, 2) order by t_bids.time desc limit 1";

	/**
	 * 用户接收的债权管理
	 */
	public static final String V_DEBT_USER_RECEIVE_TRANSFERS_MANAGEMENT = "`t_invest_transfer_details`.`id` AS `id`,`t_invest_transfer_details`.`transfer_id` AS `transer_id`,`t_invest_transfer_details`.`user_id` AS `user_id`,`t_invest_transfer_details`.`status` AS `status`,(select max(`detail`.`offer_price`) from `t_invest_transfer_details` `detail` where (`detail`.`transfer_id` = `t_invest_transfer_details`.`transfer_id`)) AS `max_price`,`t_invest_transfers`.`type` AS `type`,`t_users`.`name` AS `name`,`t_invest_transfers`.`transfer_price` AS `transfer_price`,`t_bids`.`title` AS `title`,`t_bids`.`amount` AS `amount`,`t_bids`.`apr` AS `apr`,`t_invest_transfers`.`transaction_time` AS `transaction_time`,`t_invests`.`id` AS `invest_id`,(select sum((case when (`t_bill_invests`.`status` in (-(1),-(2),-(5),-(6))) then ((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`) end)) AS `bb` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `receiving_amount`,(select sum((case when (`t_bill_invests`.`status` in (-(3),-(4),0)) then ((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`) end)) AS `bb` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `has_received_amount`,(select sum((case when (`t_bill_invests`.`status` in (-(1),-(2),-(5),-(6))) then ((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`) end)) AS `bb` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `remain_received_amount`,(select sum((case when (`t_bill_invests`.`status` in (-(1),-(2),-(5),-(6))) then `t_bill_invests`.`receive_corpus` end)) AS `bb` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `remain_received_corpus`,concat((select `t_system_options`.`_value` AS `_value` from `t_system_options` where (`t_system_options`.`_key` = 'loan_number')),(`t_bids`.`id` + '')) AS `bid_no` from ((((`t_invest_transfer_details` left join `t_invest_transfers` on((`t_invest_transfers`.`id` = `t_invest_transfer_details`.`transfer_id`))) left join `t_invests` on((`t_invests`.`id` = `t_invest_transfers`.`invest_id`))) left join `t_bids` on((`t_bids`.`id` = `t_invests`.`bid_id`))) left join `t_users` on((`t_users`.`id` = `t_bids`.`user_id`))) where (`t_invest_transfers`.`status` not in (0,-(1),-2)) ";

	/**
	 * 用户所有的转让债权
	 */
	public static final String V_DEBT_USER_TRANSFER_MANAGEMENT = "`t_invest_transfers`.`id` AS `id`,`t_invest_transfers`.`invest_id` AS `invest_id`,`t_users`.`name` AS `name`,`t_bids`.`amount` AS `amount`,`t_bids`.`title` AS `title`,`t_bids`.`apr` AS `apr`,`t_invest_transfers`.`transfer_price` AS `transfer_price`,`t_invest_transfers`.`type` AS `type`,`t_invest_transfers`.`status` AS `status`,`t_invest_transfers`.`end_time` AS `end_time`,`t_invest_transfers`.`join_times` AS `join_times`,`t_invests`.`bid_id` AS `bid_id`,`t_invests`.`user_id` AS `user_id`,(select ifnull(max(`t_invest_transfer_details`.`offer_price`),0) from `t_invest_transfer_details` where (`t_invest_transfers`.`id` = `t_invest_transfer_details`.`transfer_id`)) AS `max_price`,(select sum((case when (`t_bill_invests`.`status` in (-(1),-(2),-(5),-(6))) then ((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`) end)) AS `bb` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `receiving_amount`,(select sum((case when (`t_bill_invests`.`status` in (-(3),-(4),0)) then ((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`) end)) AS `bb` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `has_received_amount`,(select sum((case when (`t_bill_invests`.`status` in (-(1),-(2),-(5),-(6))) then ((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`) end)) AS `bb` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `remain_received_amount`,(select sum((case when (`t_bill_invests`.`status` in (-(1),-(2),-(5),-(6))) then `t_bill_invests`.`receive_corpus` end)) AS `bb` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `remain_received_corpus`,concat((select `t_system_options`.`_value` AS `_value` from `t_system_options` where (`t_system_options`.`_key` = 'loan_number')),(`t_bids`.`id` + '')) AS `bid_no` from (((`t_invest_transfers` left join `t_invests` on((`t_invests`.`id` = `t_invest_transfers`.`invest_id`))) left join `t_bids` on((`t_bids`.`id` = `t_invests`.`bid_id`))) left join `t_users` on((`t_users`.`id` = `t_bids`.`user_id`))) where 1=1 ";

	/**
	 * 成功的债权
	 */
	public static final String V_DEBT_TRANSFERS_SUCCESS = "`t_invest_transfers`.`id` AS `id`,`t_invest_transfers`.`time` AS `time`,`t_invest_transfers`.`debt_amount` AS `debt_amount`,`t_invest_transfers`.`status` AS `status`,`t_invest_transfers`.`join_times` AS `join_times`,`t_invest_transfers`.`transaction_time` AS `transaction_time`,`t_invest_transfers`.`end_time` AS `end_time`,`t_invest_transfers`.`transfer_price` AS `transfer_price`,`t_invests`.`bid_id` AS `bid_id`,`t_invests`.`user_id` AS `user_id`,`t_invests`.`amount` AS `invest_amount`,`t_users`.`name` AS `name`,t_credit_levels.image_filename AS credit_level_image_filename,t_credit_levels.order_sort AS order_sort,`t_invest_transfers`.`type` AS `type`,`t_bids`.`amount` AS `bid_amount`,`t_bids`.`period` AS `period`,`t_bids`.`apr` AS `apr`,concat((select `t_system_options`.`_value` AS `_value` from `t_system_options` where (`t_system_options`.`_key` = 'transfer_number')),(`t_invest_transfers`.`id` + '')) AS `no`,(select count(`t_bill_invests`.`id`) AS `count(t_bill_invests.id)` from `t_bill_invests` where ((`t_invests`.`id` = `t_bill_invests`.`invest_id`) and (`t_bill_invests`.`status` in (-(3),-(4),0)))) AS `has_payback_period`,(select count(`t_bill_invests`.`id`) AS `count(t_bill_invests.id)` from `t_bill_invests` where ((`t_invests`.`id` = `t_bill_invests`.`invest_id`) and (`t_bill_invests`.`status` in (-(3),-(4),-(2))))) AS `overdue_payback_period`,`t_bids`.`period_unit` AS `period_unit`,`u`.`name` AS `receive_user_name` from ((((((`t_invest_transfers` left join `t_invests` on((`t_invests`.`id` = `t_invest_transfers`.`invest_id`))) left join `t_users` on((`t_users`.`id` = `t_invests`.`user_id`))) left join `t_bids` on((`t_bids`.`id` = `t_invests`.`bid_id`))) LEFT JOIN t_credit_levels ON ((t_users.credit_level_id = t_credit_levels.id))) left join `t_products` on((`t_products`.`id` = `t_bids`.`product_id`))) left join `t_users` `u` on((`u`.`id` = `t_invest_transfers`.`transaction_user_id`))) where `t_invest_transfers`.`status` = 3 ";

	/**
	 * 转让中的债权
	 */
	public static final String V_DEBT_TRANSFERING = "`t_invest_transfers`.`id` AS `id`,`t_invest_transfers`.`time` AS `time`,`t_invest_transfers`.`debt_amount` AS `debt_amount`,`t_invest_transfers`.`status` AS `status`,`t_invest_transfers`.`join_times` AS `join_times`,`t_invest_transfers`.`end_time` AS `end_time`,`t_invest_transfers`.`transfer_price` AS `transfer_price`,`t_invests`.`bid_id` AS `bid_id`,`t_invests`.`user_id` AS `user_id`,`t_invests`.`amount` AS `invest_amount`,`t_users`.`name` AS `name`,t_credit_levels.image_filename AS credit_level_image_filename,t_credit_levels.order_sort AS order_sort,`t_invest_transfers`.`type` AS `type`,`t_bids`.`amount` AS `bid_amount`,`t_bids`.`period` AS `period`,`t_bids`.`apr` AS `apr`,(select sum((case when (`t_bill_invests`.`status` in (-(1),-(2),-(5),-(6))) then ((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`) end)) AS `bb` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `receiving_amount`,(select sum((case when (`t_bill_invests`.`status` in (-(3),-(4),0)) then ((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`) end)) AS `bb` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `has_received_amount`,(select sum((case when (`t_bill_invests`.`status` in (-(1),-(2),-(5),-(6))) then (`t_bill_invests`.`receive_corpus` - `t_bill_invests`.`real_receive_corpus`) end)) AS `cc` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `remain_received_corpus`,concat((select `t_system_options`.`_value` AS `_value` from `t_system_options` where (`t_system_options`.`_key` = 'transfer_number')),(`t_invest_transfers`.`id` + '')) AS `no`,(select count(`t_bill_invests`.`id`) AS `count(t_bill_invests.id)` from `t_bill_invests` where ((`t_bill_invests`.`invest_id` = `t_invests`.`id`) and (`t_bill_invests`.`status` in (-(3),-(4),0)))) AS `has_payback_period`,(select count(`t_bill_invests`.`id`) AS `count(t_bill_invests.id)` from `t_bill_invests` where ((`t_bill_invests`.`invest_id` = `t_invests`.`id`) and (`t_bill_invests`.`status` in (-(3),-(4),-(2))))) AS `overdue_payback_period`,`t_bids`.`period_unit` AS `period_unit` from (((((`t_invest_transfers` left join `t_invests` on((`t_invests`.`id` = `t_invest_transfers`.`invest_id`))) left join `t_users` on((`t_users`.`id` = `t_invests`.`user_id`))) left join `t_bids` on((`t_bids`.`id` = `t_invests`.`bid_id`))) LEFT JOIN t_credit_levels ON ((t_users.credit_level_id = t_credit_levels.id))) left join `t_products` on((`t_products`.`id` = `t_bids`.`product_id`))) where (`t_invest_transfers`.`status` in (1,2,4) and (`t_invest_transfers`.`end_time` > now())) ";

	/**
	 * 转让失败的债权
	 */
	public static final String V_DEBT_TRANSFER_FAILURE = "`t_invest_transfers`.`id` AS `id`,`t_invest_transfers`.`time` AS `time`,`t_invest_transfers`.`failure_time` AS `failure_time`,`t_invest_transfers`.`join_times` AS `join_times`,`t_invest_transfers`.`status` AS `status`,`t_invest_transfers`.`debt_amount` AS `debt_amount`,`t_invest_transfers`.`no_through_reason` AS `no_through_reason`,`t_invest_transfers`.`transfer_price` AS `transfer_price`,`t_invests`.`bid_id` AS `bid_id`,`t_invests`.`user_id` AS `user_id`,`t_invests`.`amount` AS `invest_amount`,`t_users`.`name` AS `name`,t_credit_levels.image_filename AS credit_level_image_filename,t_credit_levels.order_sort AS order_sort,`t_invest_transfers`.`type` AS `type`,`t_bids`.`amount` AS `bid_amount`,`t_bids`.`period` AS `period`,`t_bids`.`apr` AS `apr`,(select sum((case when (`t_bill_invests`.`status` in (-(1),-(2),-(5),-(6))) then ((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`) end)) AS `bb` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `receiving_amount`,(select sum((case when (`t_bill_invests`.`status` in (-(3),-(4),0)) then ((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`) end)) AS `bb` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `has_received_amount`,(select sum((case when (`t_bill_invests`.`status` in (-(1),-(2),-(5),-(6))) then (`t_bill_invests`.`receive_corpus` - `t_bill_invests`.`real_receive_corpus`) end)) AS `cc` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `remain_received_corpus`,concat((select `t_system_options`.`_value` AS `_value` from `t_system_options` where (`t_system_options`.`_key` = 'transfer_number')),(`t_invest_transfers`.`id` + '')) AS `no`,(select count(`t_bill_invests`.`id`) AS `count(t_bill_invests.id)` from `t_bill_invests` where ((`t_bill_invests`.`invest_id` = `t_invests`.`id`) and (`t_bill_invests`.`status` in (-(3),-(4),0)))) AS `has_payback_period`,(select count(`t_bill_invests`.`id`) AS `count(t_bill_invests.id)` from `t_bill_invests` where ((`t_bill_invests`.`invest_id` = `t_invests`.`id`) and (`t_bill_invests`.`status` in (-(3),-(4),-(2))))) AS `overdue_payback_period`,`t_bids`.`period_unit` AS `period_unit` from (((((`t_invest_transfers` left join `t_invests` on((`t_invests`.`id` = `t_invest_transfers`.`invest_id`))) left join `t_users` on((`t_users`.`id` = `t_invests`.`user_id`))) left join `t_bids` on((`t_bids`.`id` = `t_invests`.`bid_id`))) LEFT JOIN t_credit_levels ON ((t_users.credit_level_id = t_credit_levels.id))) left join `t_products` on((`t_products`.`id` = `t_bids`.`product_id`))) where (`t_invest_transfers`.`status` in (-(2),-(3),-(5)) or ((`t_invest_transfers`.`status` in (1,2,4)) and (`t_invest_transfers`.`end_time` <= now()))) ";

	/**
	 * 未通过的债权
	 */
	public static final String V_DEBT_NO_PASS_TRANSFERS = "`t_invest_transfers`.`id` AS `id`,`t_invest_transfers`.title AS title,`t_invest_transfers`.`time` AS `time`,`t_invest_transfers`.start_time AS start_time,`t_invest_transfers`.`status` AS `status`,`t_invest_transfers`.`debt_amount` AS `debt_amount`,`t_invest_transfers`.`no_through_reason` AS `no_through_reason`,`t_invest_transfers`.`transfer_price` AS `transfer_price`,`t_invest_transfers`.`failure_time` AS `failure_time`,`t_invests`.`bid_id` AS `bid_id`,`t_invests`.`user_id` AS `user_id`,`t_invests`.`amount` AS `invest_amount`,`t_users`.`name` AS `name`,t_credit_levels.image_filename AS credit_level_image_filename,t_credit_levels.order_sort AS order_sort,`t_invest_transfers`.`type` AS `type`,`t_bids`.`amount` AS `bid_amount`,`t_bids`.`period` AS `period`,`t_bids`.`apr` AS `apr`,(select sum((case when (`t_bill_invests`.`status` in (-(1),-(2),-(5),-(6))) then ((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`) end)) AS `bb` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `receiving_amount`,(select sum((case when (`t_bill_invests`.`status` in (-(3),-(4),0)) then ((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`) end)) AS `bb` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `has_received_amount`,(select sum((case when (`t_bill_invests`.`status` in (-(1),-(2),-(5),-(6))) then (`t_bill_invests`.`receive_corpus` - `t_bill_invests`.`real_receive_corpus`) end)) AS `cc` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `remain_received_corpus`,concat((select `t_system_options`.`_value` AS `_value` from `t_system_options` where (`t_system_options`.`_key` = 'transfer_number')),(`t_invest_transfers`.`id` + '')) AS `no`,(select count(`t_bill_invests`.`id`) AS `count(t_bill_invests.id)` from `t_bill_invests` where ((`t_bill_invests`.`invest_id` = `t_invests`.`id`) and (`t_bill_invests`.`status` in (-(3),-(4),0)))) AS `has_payback_period`,(select count(`t_bill_invests`.`id`) AS `count(t_bill_invests.id)` from `t_bill_invests` where ((`t_bill_invests`.`invest_id` = `t_invests`.`id`) and (`t_bill_invests`.`status` in (-(3),-(4),-(2))))) AS `overdue_payback_period`,`t_bids`.`period_unit` AS `period_unit` from (((((`t_invest_transfers` left join `t_invests` on((`t_invests`.`id` = `t_invest_transfers`.`invest_id`))) left join `t_users` on((`t_users`.`id` = `t_invests`.`user_id`))) left join `t_bids` on((`t_bids`.`id` = `t_invests`.`bid_id`))) LEFT JOIN t_credit_levels ON ((t_users.credit_level_id = t_credit_levels.id))) left join `t_products` on((`t_products`.`id` = `t_bids`.`product_id`))) where (`t_invest_transfers`.`status` = -(1)) ";

	/**
	 * 审核中的债权
	 */
	public static final String V_DEBT_AUDITING_TRANSFERS = "`t_invest_transfers`.`id` AS `id`,`t_invest_transfers`.`time` AS `time`,`t_invest_transfers`.`status` AS `status`,`t_invest_transfers`.`debt_amount` AS `debt_amount`,`t_invest_transfers`.`no_through_reason` AS `no_through_reason`,`t_invest_transfers`.`transfer_price` AS `transfer_price`,`t_invests`.`bid_id` AS `bid_id`,`t_invests`.`user_id` AS `user_id`,`t_invests`.`amount` AS `invest_amount`,`t_users`.`name` AS `name`,`t_credit_levels`.`image_filename` AS `credit_level_image_filename`,`t_credit_levels`.order_sort AS `order_sort`,`t_invest_transfers`.`type` AS `type`,`t_bids`.`amount` AS `bid_amount`,`t_bids`.`image_filename` AS `image_filename`,`t_bids`.`period` AS `period`,`t_bids`.`apr` AS `apr`,(select sum((case when (`t_bill_invests`.`status` in (-(1),-(2),-(5),-(6))) then ((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`) end)) AS `bb` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `receiving_amount`,(select sum((case when (`t_bill_invests`.`status` in (-(3),-(4),0)) then ((`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`) + `t_bill_invests`.`overdue_fine`) end)) AS `bb` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `has_received_amount`,(select sum((case when (`t_bill_invests`.`status` in (-(1),-(2),-(5),-(6))) then (`t_bill_invests`.`receive_corpus` - `t_bill_invests`.`real_receive_corpus`) end)) AS `cc` from `t_bill_invests` where (`t_bill_invests`.`invest_id` = `t_invests`.`id`)) AS `remain_received_corpus`,concat((select `t_system_options`.`_value` AS `_value` from `t_system_options` where (`t_system_options`.`_key` = 'transfer_number')),(`t_invest_transfers`.`id` + '')) AS `no`,(select count(`t_bill_invests`.`id`) AS `count(t_bill_invests.id)` from `t_bill_invests` where ((`t_bill_invests`.`invest_id` = `t_invests`.`id`) and (`t_bill_invests`.`status` in (-(3),-(4),0)))) AS `has_payback_period`,(select count(`t_bill_invests`.`id`) AS `count(t_bill_invests.id)` from `t_bill_invests` where ((`t_bill_invests`.`invest_id` = `t_invests`.`id`) and (`t_bill_invests`.`status` in (-(3),-(4),-(2))))) AS `overdue_payback_period`,`t_bids`.`period_unit` AS `period_unit` from (((((`t_invest_transfers` left join `t_invests` on((`t_invests`.`id` = `t_invest_transfers`.`invest_id`))) left join `t_users` on((`t_users`.`id` = `t_invests`.`user_id`))) left join `t_bids` on((`t_bids`.`id` = `t_invests`.`bid_id`))) LEFT JOIN t_credit_levels ON ((t_users.credit_level_id = t_credit_levels.id))) left join `t_products` on((`t_products`.`id` = `t_bids`.`product_id`))) where (`t_invest_transfers`.`status` = 0 and `t_invests`.`transfer_status` = 1) ";

	/**
	 * 债权操作记录
	 */
	public static final String V_DEBT_AUCTION_RECORDS = "`t_invest_transfer_details`.`id` AS `id`,`t_invest_transfers`.`debt_amount` AS `debt_amount`,`t_invest_transfer_details`.`offer_price` AS `offer_price`,`t_users`.`name` AS `name`,`t_invest_transfer_details`.`status` AS `status`,`t_invest_transfer_details`.`time` AS `time`,`t_invest_transfer_details`.`transfer_id` AS `transfer_id` from ((`t_invest_transfer_details` left join `t_invest_transfers` on((`t_invest_transfer_details`.`transfer_id` = `t_invest_transfers`.`id`))) left join `t_users` on((`t_invest_transfer_details`.`user_id` = `t_users`.`id`))) where 1=1 ";

	/**
	 * 数据库操作
	 */
	public static final String V_DB_OPERATIONS = "t_db_operations.id,t_db_operations.time,t_db_operations.ip,t_db_operations.type,t_db_operations.filename,t_supervisors.name as supervisor_name,t_supervisors.ukey AS supervisor_ukey from t_db_operations left join t_supervisors on t_db_operations.supervisor_id = t_supervisors.id ";

	/**
	 * 信用等级
	 */
	public static final String V_CREDIT_LEVELS = "`cl`.`id` AS `id`,`cl`.`name` AS `name`,`cl`.`image_filename` AS `image_filename`,`cl`.`is_enable` AS `is_enable`,`cl`.`min_credit_score` AS `min_credit_score`,`cl`.`min_audit_items` AS `min_audit_items`,if((`cl`.`is_allow_overdue` = 1),'是','否') AS `is_allow_overdue`,`cl`.`suggest` AS `suggest`,(select group_concat(`t_dict_audit_items`.`name` separator '、') AS `must_items` from `t_dict_audit_items` where find_in_set(`t_dict_audit_items`.`id`,`cl`.`must_items`)) AS `must_items` from `t_credit_levels` `cl` where 1=1 ";

	/**
	 * 自动投标确认
	 */
	public static final String V_CONFIRM_AUTOINVEST_BIDS = "`t_bids`.`id` AS `id`,`t_bids`.`user_id` AS `user_id`,`t_bids`.`amount` AS `amount`,`t_bids`.`period` AS `period`,`t_bids`.`min_invest_amount` AS `min_invest_amount`,`t_bids`.`status` AS `status`,`t_bids`.`loan_schedule` AS `loan_schedule`,`t_bids`.`has_invested_amount` AS `has_invested_amount`,`t_bids`.`audit_time` AS `audit_time`,`t_users`.`credit_level_id` AS `credit_level_id`,`f_credit_levels`(`t_users`.`id`) AS `num`,`t_bids`.`period_unit` AS `period_unit`,`t_bids`.`average_invest_amount` AS `average_invest_amount`,`t_bids`.`apr` AS `apr`,`t_products`.`min_interest_rate` AS `min_interest_rate`,`t_products`.`max_interest_rate` AS `max_interest_rate`,`t_products`.`loan_type` AS `loan_type` from ((`t_bids` left join `t_products` on((`t_bids`.`product_id` = `t_products`.`id`))) left join `t_users` on((`t_bids`.`user_id` = `t_users`.`id`))) where ((`t_bids`.`loan_schedule` < 95) and (`t_products`.`loan_type` <> 3) and (`t_bids`.`status` in (1,2)) and (`t_products`.`is_deal_password` = 0)) ";

	/**
	 * 账单还款记录
	 */
	public static final String V_BILL_REPAYMENT_RECORD = "`a`.`id` AS `id`,`a`.`bid_id` AS `bid_id`,`a`.`title` AS `title`,sum(((`a`.`repayment_corpus` + `a`.`repayment_interest`) + `a`.`overdue_fine`)) AS `current_pay_amount`,`a`.`overdue_mark` AS `overdue_mark`,`a`.`status` AS `status`,`a`.`repayment_time` AS `repayment_time`,`a`.`real_repayment_time` AS `real_repayment_time` from (`t_bills` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) where 1=1 ";

	/**
	 * 已收款的账单统计
	 */
	public static final String V_BILL_RECEVIABLE_STATISTICAL = "`t_bills`.`id` AS `id`,year(`t_bills`.`repayment_time`) AS `year`,month(`t_bills`.`repayment_time`) AS `month`,(select count(`t`.`id`) from `t_bills` `t` where ((year(`t`.`repayment_time`) = year(`t_bills`.`repayment_time`)) and (month(`t`.`repayment_time`) = month(`t_bills`.`repayment_time`)))) AS `bill_accounts`,(select sum(((`t`.`repayment_corpus` + `t`.`repayment_interest`) + `t`.`overdue_fine`)) from `t_bills` `t` where ((year(`t`.`repayment_time`) = year(`t_bills`.`repayment_time`)) and (month(`t`.`repayment_time`) = month(`t_bills`.`repayment_time`)))) AS `amounts_receivable`,(select count(`t`.`id`) from `t_bills` `t` where ((`t`.`status` in (-(3),0)) and (year(`t`.`repayment_time`) = year(`t_bills`.`repayment_time`)) and (month(`t`.`repayment_time`) = month(`t_bills`.`repayment_time`)))) AS `bills_received`,(select sum(`t`.`amount`) from (`t_bids` `t` join `t_bills` `d` on((`t`.`id` = `d`.`bid_id`))) where ((year(`d`.`repayment_time`) = year(`t_bills`.`repayment_time`)) and (month(`d`.`repayment_time`) = month(`t_bills`.`repayment_time`)))) AS `bids_amount`,ifnull((select sum(((`t`.`repayment_corpus` + `t`.`repayment_interest`) + `t`.`overdue_fine`)) from `t_bills` `t` where ((`t`.`status` in (-(3),0)) and (year(`t`.`repayment_time`) = year(`t_bills`.`repayment_time`)) and (month(`t`.`repayment_time`) = month(`t_bills`.`repayment_time`)))),0) AS `amount_received`,round(((count((case when (`t_bills`.`status` = 0) then `t_bills`.`id` end)) / count(`t_bills`.`id`)) * 100),0) AS `bills_timely_completion_rate`,(select count(`t`.`id`) from `t_bills` `t` where ((`t`.`overdue_mark` in (-(1),-(2),-(3))) and (year(`t`.`repayment_time`) = year(`t_bills`.`repayment_time`)) and (month(`t`.`repayment_time`) = month(`t_bills`.`repayment_time`)))) AS `overdue_counts`,round(((count((case when (`t_bills`.`overdue_mark` in (-(1),-(2),-(3))) then `t_bills`.`id` end)) / count(`t_bills`.`id`)) * 100),0) AS `bills_overdue_rate`,round(((count((case when (`t_bills`.`status` in (0,-(3))) then `t_bills`.`id` end)) / count(`t_bills`.`id`)) * 100),0) AS `bills_completed_rate`,(select count(`t`.`id`) from `t_bills` `t` where ((`t`.`status` in (-(1),-(2))) and (`t`.`overdue_mark` in (-(1),-(2),-(3))) and (year(`t`.`repayment_time`) = year(`t_bills`.`repayment_time`)) and (month(`t`.`repayment_time`) = month(`t_bills`.`repayment_time`)))) AS `bills_overdue_noreceive`,(select sum(((`t`.`repayment_corpus` + `t`.`repayment_interest`) + `t`.`overdue_fine`)) from `t_bills` `t` where ((`t`.`status` in (-(1),-(2))) and (year(`t`.`repayment_time`) = year(`t_bills`.`repayment_time`)) and (month(`t`.`repayment_time`) = month(`t_bills`.`repayment_time`)))) AS `uncollected_amount`,round(((count((case when (`t_bills`.`status` in (-(1),-(2))) then `t_bills`.`id` end)) / count(`t_bills`.`id`)) * 100),0) AS `uncollected_rate` from `t_bills` where ((month(`t_bills`.`repayment_time`) <= month(now())) and (year(`t_bills`.`repayment_time`) <= year(now()))) ";

	/**
	 * 最近待收的账单
	 */
	public static final String V_BILL_RECENTLY_PENDING = "`a`.`id` AS `id`,concat(`d`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`id` AS `bid_id`,`c`.`id` AS `user_id`,`a`.`periods` AS `period`,`b`.`title` AS `title`,`c`.`device_user_id` AS `device_user_id`,`c`.`channel_id` AS `channel_id`,`c`.`device_type` AS `device_type`,`c`.`is_bill_push` AS `is_bill_push`,concat(cast(floor((timestampdiff(MINUTE,now(),`a`.`repayment_time`) / 1440)) as char(10) charset utf8),'天') AS `repay_time` from (((`t_bills` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_users` `c` on((`b`.`user_id` = `c`.`id`))) join `t_system_options` `d`) where ((`a`.`status` = -(1)) and (`d`.`_key` = 'loan_bill_number')) ";

	/**
	 * 收款中的逾期账单
	 */
	public static final String V_BILL_RECEIVING_OVERDUE = "`a`.`id` AS `id`,year(`a`.`repayment_time`) AS `year`,month(`a`.`repayment_time`) AS `month`,`b`.`id` AS `bid_id`,concat(`d`.`_value`,`a`.`id`) AS `bill_no`,`c`.`name` AS `name`,concat(`e`.`_value`,`b`.`id`) AS `bid_no`,`b`.`amount` AS `amount`,`b`.`apr` AS `apr`,`a`.`title` AS `title`,(select concat(`a`.`periods`,'/',count(`t`.`id`)) AS `concat(``a``.``periods``,'/',count(``t``.``id``))` from `t_bills` `t` where (`t`.`bid_id` = `a`.`bid_id`)) AS `period`,`a`.`repayment_time` AS `repayment_time`,(case when ((`a`.`repayment_time` - now()) > 0) then '0' else concat(concat(cast(floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) as char(10) charset utf8),'天'),concat(cast(floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) as char charset utf8),'\r\n\r\n小时'),concat(cast(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) - (floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) * 1440)) - (floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) * 60)) as char charset utf8)),'分') end) AS `overdue_time`,`a`.`overdue_fine` AS `late_penalty`,(select count(`t_bills`.`id`) AS `count(id)` from `t_bills` where ((`t_bills`.`bid_id` = `b`.`id`) and (`t_bills`.`overdue_mark` in (-(1),-(2),-(3))))) AS `overdue_count`,(select `a`.`name` AS `name` from `t_supervisors` `a` where (`c`.`assigned_to_supervisor_id` = `a`.`id`)) AS `supervisor_name`,(select `a`.`name` AS `name` from `t_supervisors` `a` where (`b`.`manage_supervisor_id` = `a`.`id`)) AS `supervisor_name2` from ((((`t_bills` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_users` `c` on((`b`.`user_id` = `c`.`id`))) join `t_system_options` `d`) join `t_system_options` `e`) where ((`d`.`_key` = 'loan_bill_number') and (`e`.`_key` = 'loan_number') and (`a`.`overdue_mark` in (-(1),-(2),-(3))) and (`a`.`status` in (-(1),-(2)))) ";

	/**
	 * 收款中的理财账单
	 */
	public static final String V_BILL_RECEIVING = "`a`.`id` AS `id`,year(`a`.`repayment_time`) AS `year`,month(`a`.`repayment_time`) AS `month`,`b`.`id` AS `bid_id`,concat(`d`.`_value`,cast(`a`.`id` as char charset utf8)) AS `bill_no`,`c`.`name` AS `name`,concat(`e`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`amount` AS `amount`,`b`.`apr` AS `apr`,`a`.`title` AS `title`,(select concat(`a`.`periods`,'/',count(`t1`.`id`)) from `t_bills` `t1` where (`t1`.`bid_id` = `a`.`bid_id`)) AS `period`,`a`.`repayment_time` AS `repayment_time`,(case when ((`a`.`repayment_time` - now()) > 0) then '0' else concat(concat(cast(floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) as char(10) charset utf8),'天'),concat(cast(floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) as char charset utf8),'小时'),concat(cast(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) - (floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) * 1440)) - (floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) * 60)) as char charset utf8)),'分') end) AS `overdue_time`,(select count(`t_bills`.`id`) AS `count(id)` from `t_bills` where ((`t_bills`.`bid_id` = `b`.`id`) and (`t_bills`.`overdue_mark` in (-(1),-(2),-(3))))) AS `overdue_count`,(select `a`.`name` AS `name` from `t_supervisors` `a` where (`c`.`assigned_to_supervisor_id` = `a`.`id`)) AS `supervisor_name`,(select `a`.`name` AS `name` from `t_supervisors` `a` where (`b`.`manage_supervisor_id` = `a`.`id`)) AS `supervisor_name2` from ((((`t_bills` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_users` `c` on((`b`.`user_id` = `c`.`id`))) join `t_system_options` `d`) join `t_system_options` `e`) where ((`d`.`_key` = 'loan_bill_number') and (`e`.`_key` = 'loan_number') and (`a`.`status` in (-(1),-(2)))) ";

	/**
	 * 逾期的账单
	 */
	public static final String V_BILL_OVERDUE = "`a`.`id` AS `id`,`c`.`id` AS `user_id`,year(`a`.`repayment_time`) AS `year`,month(`a`.`repayment_time`) AS `month`,`b`.`id` AS `bid_id`,concat(`d`.`_value`,cast(`a`.`id` as char charset utf8)) AS `bill_no`,`c`.`name` AS `name`,concat(`e`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`amount` AS `amount`,`b`.`apr` AS `apr`,`a`.`title` AS `title`,(select concat(`a`.`periods`,'/',count(`t`.`id`)) AS `concat(``a``.``periods``,'/',count(``t``.``id``))` from `t_bills` `t` where (`t`.`bid_id` = `a`.`bid_id`)) AS `period`,`a`.`repayment_time` AS `repayment_time`,(case when ((`a`.`repayment_time` - now()) > 0) then '0' else concat(concat(cast(floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) as char(10) charset utf8),'天'),concat(cast(floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) as char charset utf8),'小时'),concat(cast(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) - (floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) * 1440)) - (floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) * 60)) as char charset utf8)),'分') end) AS `overdue_time`,(select count(`t_bills`.`id`) AS `count(id)` from `t_bills` where ((`t_bills`.`bid_id` = `b`.`id`) and (`t_bills`.`overdue_mark` in (-(1),-(2),-(3))))) AS `overdue_count`,`f`.`name` AS `supervisor_name`,`f`.`id` AS `supervisor_id` from (((((`t_bills` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_users` `c` on((`b`.`user_id` = `c`.`id`))) join `t_system_options` `d`) join `t_system_options` `e`) join `t_supervisors` `f` on((`f`.`id` = `b`.`manage_supervisor_id`))) where ((`d`.`_key` = 'loan_bill_number') and (`e`.`_key` = 'loan_number') and (`a`.`overdue_mark` = -(2))) ";

	/**
	 * 月到期账单
	 */
	public static final String V_BILL_MONTH_MATURITY = "`a`.`id` AS `id`,`c`.`id` AS `user_id`,year(`a`.`repayment_time`) AS `year`,month(`a`.`repayment_time`) AS `month`,`b`.`id` AS `bid_id`,concat(`d`.`_value`,cast(`a`.`id` as char charset utf8)) AS `bill_no`,`c`.`name` AS `name`,concat(`e`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`amount` AS `amount`,`b`.`apr` AS `apr`,`a`.`title` AS `title`,(select concat(`a`.`periods`,'/',count(`t`.`id`)) from `t_bills` `t` where (`t`.`bid_id` = `a`.`bid_id`)) AS `period`,`a`.`repayment_time` AS `repayment_time`,(case when ((`a`.`repayment_time` - now()) > 0) then '0' else concat(concat(cast(floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) as char(10) charset utf8),'天'),concat(cast(floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) as char charset utf8),'小时'),concat(cast(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) - (floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) * 1440)) - (floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) * 60)) as char charset utf8)),'分') end) AS `overdue_time`,(select count(`t_bills`.`id`) AS `count(id)` from `t_bills` where ((`t_bills`.`bid_id` = `b`.`id`) and (`t_bills`.`overdue_mark` in (-(1),-(2),-(3))))) AS `overdue_count`,`f`.`name` AS `supervisor_name`,`f`.`id` AS `supervisor_id` from (((((`t_bills` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_users` `c` on((`b`.`user_id` = `c`.`id`))) join `t_system_options` `d`) join `t_system_options` `e`) join `t_supervisors` `f` on((`f`.`id` = `b`.`manage_supervisor_id`))) where ((`d`.`_key` = 'loan_bill_number') and (`e`.`_key` = 'loan_number') and (year(`a`.`repayment_time`) = year(now())) and (month(`a`.`repayment_time`) = month(now())) and (`a`.`overdue_mark` not in (-(2),-(3)))) ";

	/**
	 * 贷款账单
	 */
	public static final String V_BILL_LOAN = "`a`.`id` AS `id`,`c`.`id` AS `user_id`,`b`.`id` AS `bid_id`,`a`.`title` AS `title`,`a`.`overdue_mark` AS `is_overdue`,sum(((`a`.`repayment_corpus` + `a`.`repayment_interest`) + `a`.`overdue_fine`)) AS `repayment_amount`,`a`.`status` AS `status`,`a`.`repayment_time` AS `repayment_time`,`a`.`real_repayment_time` AS `real_repayment_time` from (((`t_bills` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_users` `c` on((`b`.`user_id` = `c`.`id`))) join `t_system_options` `d`) where (`d`.`_key` = 'loan_bill_number') ";

	/**
	 * 还款中的本息账单
	 */
	public static final String V_BILL_INVESTS_PRINCIPAL_ADVANCES = "`a`.`id` AS `id`,year(`a`.`real_receive_time`) AS `year`,month(`a`.`real_receive_time`) AS `month`,concat(`f`.`_value`,cast(`a`.`id` as char charset utf8)) AS `bill_no`,`d`.`name` AS `invest_name`,(select concat(`a`.`periods`,'/',count(`t`.`id`)) from `t_bills` `t` where (`t`.`bid_id` = `a`.`bid_id`)) AS `period`,(select sum(((`t`.`receive_corpus` + `t`.`receive_interest`) + `t`.`overdue_fine`)) from `t_bill_invests` `t` where (`t`.`id` = `a`.`id`)) AS `pay_amount`,`a`.`title` AS `title`,`b`.`id` AS `bid_id`,concat(`g`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`e`.`name` AS `name`,`a`.`receive_time` AS `receive_time`,`a`.`real_receive_time` AS `real_receive_time`,`a`.`status` AS `status`,(select `a`.`name` AS `name` from `t_supervisors` `a` where (`d`.`assigned_to_supervisor_id` = `a`.`id`)) AS `supervisor_name`,(select `a`.`name` AS `name` from `t_supervisors` `a` where (`b`.`manage_supervisor_id` = `a`.`id`)) AS `supervisor_name2` from (((((`t_bill_invests` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_users` `d` on((`a`.`user_id` = `d`.`id`))) join `t_users` `e` on((`b`.`user_id` = `e`.`id`))) join `t_system_options` `f`) join `t_system_options` `g`) where ((`f`.`_key` = 'invests_bill_number') and (`g`.`_key` = 'loan_number') and (`a`.`status` = -(3))) ";

	/**
	 * 待还款的理财账单
	 */
	public static final String V_BILL_INVESTS_PENDING_PAYMENT = "`a`.`id` AS `id`,year(`a`.`receive_time`) AS `year`,month(`a`.`receive_time`) AS `month`,concat(`f`.`_value`,cast(`a`.`id` as char charset utf8)) AS `bill_no`,`d`.`name` AS `invest_name`,(select concat(`a`.`periods`,'/',count(`t`.`id`)) from `t_bills` `t` where (`t`.`bid_id` = `a`.`bid_id`)) AS `period`,(select sum(((`t`.`receive_corpus` + `t`.`receive_interest`) + `t`.`overdue_fine`)) from `t_bill_invests` `t` where (`t`.`id` = `a`.`id`)) AS `pay_amount`,`a`.`title` AS `title`,`a`.`id` AS `invest_id`,`b`.`id` AS `bid_id`,concat(`g`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`e`.`name` AS `name`,`a`.`receive_time` AS `receive_time`,(case when ((`a`.`receive_time` - now()) > 0) then '0' else concat(concat(cast(floor((timestampdiff(MINUTE,`a`.`receive_time`,now()) / 1440)) as char(10) charset utf8),'天'),concat(cast(floor(((timestampdiff(MINUTE,`a`.`receive_time`,now()) % 1440) / 60)) as char charset utf8),'小\r\n\r\n时'),concat(cast(((timestampdiff(MINUTE,`a`.`receive_time`,now()) - (floor((timestampdiff(MINUTE,`a`.`receive_time`,now()) / 1440)) * 1440)) - (floor(((timestampdiff(MINUTE,`a`.`receive_time`,now()) % 1440) / 60)) * 60)) as char charset utf8)),'分') end) AS `overdue_time`,(select count(`t1`.`id`) AS `COUNT(t1.id)` from `t_bill_invests` `t1` where ((`t1`.`user_id` = `a`.`user_id`) and (`t1`.`status` in (-(1),-(2),-(5),-(6))))) AS `unpaid_bills`,`a`.`status` AS `status`,(select `a`.`name` AS `name` from `t_supervisors` `a` where (`d`.`assigned_to_supervisor_id` = `a`.`id`)) AS `supervisor_name`,(select `a`.`name` AS `name` from `t_supervisors` `a` where (`b`.`manage_supervisor_id` = `a`.`id`)) AS `supervisor_name2` from (((((`t_bill_invests` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_users` `d` on((`a`.`user_id` = `d`.`id`))) join `t_users` `e` on((`b`.`user_id` = `e`.`id`))) join `t_system_options` `f`) join `t_system_options` `g`) where ((`f`.`_key` = 'invests_bill_number') and (`g`.`_key` = 'loan_number') and (`a`.`status` in (-(5),-(6)))) ";

	/**
	 * 已还完投资账单统计
	 */
	public static final String V_BILL_INVESTS_PAYABLES_STATISTICS = "`a`.`id` AS `id`,year(`a`.`receive_time`) AS `year`,month(`a`.`receive_time`) AS `month`,ifnull(count((case when ((year(`a`.`receive_time`) = year(now())) and (month(`a`.`receive_time`) = month(now()))) then `a`.`id` end)),0) AS `payables_bills`,ifnull(sum((case when ((year(`a`.`receive_time`) = year(now())) and (month(`a`.`receive_time`) = month(now()))) then ((`a`.`receive_corpus` + `a`.`receive_interest`) + `a`.`overdue_fine`) end)),0) AS `payables_amount`,ifnull(count((case when (`a`.`status` in (-(3),-(4),0)) then `a`.`id` end)),0) AS `has_paid_bills`,ifnull(sum((case when (`a`.`status` in (-(3),-(4),0)) then ((`a`.`overdue_fine` + `a`.`real_receive_corpus`) + `a`.`real_receive_interest`) end)),0) AS `has_paid_amount`,ifnull(count((case when (`a`.`status` = 0) then `a`.`id` end)),0) AS `normal_paid_bills`,ifnull(round(((count((case when (`a`.`status` = 0) then `a`.`id` end)) / count(`a`.`id`)) * 100),0),0) AS `ontime_complete_rate`,ifnull(count((case when (`a`.`status` = -(3)) then `a`.`id` end)),0) AS `principal_advances_bills`,ifnull(sum((case when (`a`.`status` = -(3)) then ((`a`.`overdue_fine` + `a`.`real_receive_corpus`) + `a`.`real_receive_interest`) end)),0) AS `principal_advances_amount`,ifnull(round(((count((case when (`a`.`status` = -(3)) then `a`.`id` end)) / count(`a`.`id`)) * 100),0),0) AS `principal_advances_rate`,ifnull(count((case when (`a`.`status` in (-(1),-(2),-(5),-(6))) then `a`.`id` end)),0) AS `nopaid_bills`,ifnull(sum((case when (`a`.`status` in (-(1),-(2),-(5),-(6))) then ((`a`.`receive_corpus` + `a`.`receive_interest`) + `a`.`overdue_fine`) end)),0) AS `nopaid_amount`,ifnull(round(((count((case when (`a`.`status` in (-(1),-(2),-(5),-(6))) then `a`.`id` end)) / count(`a`.`id`)) * 100),0),0) AS `nopaid_rate` from `t_bill_invests` `a` where ((year(`a`.`receive_time`) <= year(now())) and (month(`a`.`receive_time`) <= month(now())) and (`a`.`status` <> -(7))) group by year(`a`.`receive_time`),month(`a`.`receive_time`) ";

	/**
	 * 还款的理财账单
	 */
	public static final String V_BILL_INVESTS_PAID = "`a`.`id` AS `id`,year(`a`.`real_receive_time`) AS `year`,month(`a`.`real_receive_time`) AS `month`,concat(`f`.`_value`,cast(`a`.`id` as char charset utf8)) AS `bill_no`,`d`.`name` AS `invest_name`,(select concat(`a`.`periods`,'/',count(`t`.`id`)) from `t_bills` `t` where (`t`.`bid_id` = `a`.`bid_id`)) AS `period`,(select sum(((`t`.`receive_corpus` + `t`.`receive_interest`) + `t`.`overdue_fine`)) from `t_bill_invests` `t` where (`t`.`id` = `a`.`id`)) AS `pay_amount`,`a`.`title` AS `title`,`b`.`id` AS `bid_id`,concat(`g`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`e`.`name` AS `name`,`a`.`receive_time` AS `receive_time`,`a`.`real_receive_time` AS `real_receive_time`,`a`.`status` AS `status`,(select `a`.`name` AS `name` from `t_supervisors` `a` where (`e`.`assigned_to_supervisor_id` = `a`.`id`)) AS `supervisor_name`,(select `a`.`name` AS `name` from `t_supervisors` `a` where (`b`.`manage_supervisor_id` = `a`.`id`)) AS `supervisor_name2` from ((((((`t_bill_invests` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_supervisors` `c`) join `t_users` `d` on((`a`.`user_id` = `d`.`id`))) join `t_users` `e` on((`b`.`user_id` = `e`.`id`))) join `t_system_options` `f`) join `t_system_options` `g`) where ((`f`.`_key` = 'invests_bill_number') and (`g`.`_key` = 'loan_number') and (`a`.`status` in (-(4),0))) ";

	public static final String V_BILL_INVESTS_OVERDUE_UNPAID = "";

	public static final String V_BILL_INVEST_STATISTICS = "";

	/**
	 * 理财账单详情
	 */
	public static final String V_BILL_INVEST_DETAIL = "`a`.`id` AS `id`,`a`.`user_id` AS `user_id`,`a`.`invest_id` AS `invest_id`,`a`.`periods` AS `current_period`,`e`.`name` AS `name`,`a`.`bid_id` AS `bid_id`,`a`.`title` AS `title`,`b`.`audit_time` AS `audit_time`,`a`.`receive_time` AS `receive_time`,`c`.`name` AS `repayment_type`,`b`.`apr` AS `apr`,`b`.`amount` AS `amount`,(select count(`t`.`id`) AS `COUNT(t.id)` from `t_bills` `t` where (`t`.`bid_id` = `a`.`bid_id`)) AS `loan_periods`,`a`.`receive_corpus` AS `receive_corpus`,`d`.`amount` AS `invest_amount`,(select sum((`t`.`receive_corpus` + `t`.`receive_interest`)) AS `ss` from `t_bill_invests` `t` where (`t`.`id` = `a`.`id`)) AS `should_received_amount`,(select sum(((`t`.`receive_corpus` + `t`.`receive_interest`) + `t`.`overdue_fine`)) AS `ss` from `t_bill_invests` `t` where (`t`.`id` = `a`.`id`)) AS `current_receive_amount`,(select sum(((`t`.`receive_corpus` + `t`.`receive_interest`) + `t`.`overdue_fine`)) AS `receive_amounts` from `t_bill_invests` `t` where ((`t`.`user_id` = `a`.`user_id`) and (`t`.`bid_id` = `a`.`bid_id`) and (`t`.`status` <> -(7)) and (`t`.`invest_id` = `a`.`invest_id`))) AS `should_receive_all_amount`,ifnull((select sum(((`t`.`receive_corpus` + `t`.`receive_interest`) + `t`.`overdue_fine`)) AS `amount` from `t_bill_invests` `t` where ((`t`.`user_id` = `a`.`user_id`) and (`t`.`bid_id` = `a`.`bid_id`) and (`t`.`status` in (-(3),-(4),0)) and (`t`.`invest_id` = `a`.`invest_id`))),0) AS `has_received_amount`,(select count(`t`.`id`) AS `couont` from `t_bill_invests` `t` where ((`t`.`user_id` = `a`.`user_id`) and (`t`.`bid_id` = `a`.`bid_id`) and (`t`.`status` in (-(3),-(4),0)) and (`t`.`invest_id` = `a`.`invest_id`))) AS `has_received_periods`,(select count(`t1`.`id`) AS `count(``t1``.``id``)` from `t_bills` `t1` where ((`t1`.`bid_id` = `a`.`bid_id`) and (`t1`.`status` in (-(3),0)))) AS `has_payed_periods`,(select sum(((`t`.`repayment_corpus` + `t`.`repayment_interest`) + `t`.`overdue_fine`)) AS `amount` from `t_bills` `t` where (`t`.`bid_id` = `a`.`bid_id`)) AS `loan_principal_interest`,concat(`f`.`_value`,cast(`a`.`id` as char charset utf8)) AS `invest_number` from (((((`t_bill_invests` `a` left join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) left join `t_dict_bid_repayment_types` `c` on((`b`.`repayment_type_id` = `c`.`id`))) left join `t_invests` `d` on(((`d`.`bid_id` = `a`.`bid_id`) and (`d`.`user_id` = `a`.`user_id`) and (`d`.`id` = `a`.`invest_id`)))) left join `t_users` `e` on((`a`.`user_id` = `e`.`id`))) join `t_system_options` `f`) where ((`f`.`_key` = 'invests_bill_number') and (`a`.`status` <> -(7))) ";
	/**
	 * 取现
	 */
	public static final String V_RECHARGER ="`a`.`id` AS `id`,`a`.`user_id` AS `user_id`,`a`.`time` AS `time`,`a`.`payment_gateway_id` AS `payment_gateway_id`,`a`.`pay_number` AS `pay_number`,`a`.`amount` AS `amount`,`a`.`is_completed` AS `is_completed`,`a`.`completed_time` AS `completed_time`,`a`.`order_no` AS `order_no`,`a`.`type` AS `type` from `t_user_recharge_details` a where 1=1 ";
	/**
	 * 理财账单
	 */
	public static final String V_BILL_INVEST = "`a`.`id` AS `id`,`a`.`receive_corpus` AS `receive_corpus`,`c`.`id` AS `user_id`,`a`.`bid_id` AS `bid_id`,`a`.`title` AS `title`,((`a`.`receive_corpus` + `a`.`receive_interest`) + `a`.`overdue_fine`) AS `income_amounts`,`a`.`status` AS `status`,`a`.`receive_time` AS `repayment_time`,`a`.`real_receive_time` AS `real_repayment_time` from ((`t_bill_invests` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_users` `c` on((`a`.`user_id` = `c`.`id`))) where 1=1 ";

	/**
	 * 根据bid_id 查询状态
	 */
	public static final String V_BILL_bid= "`b`.`status` AS `status` from  `t_bids` `b` where 1=1";

	/**
	 * 已还款的账单
	 */
	public static final String V_BILL_HASPAYED = "`a`.`id` AS `id`,year(`a`.`repayment_time`) AS `year`,month(`a`.`repayment_time`) AS `month`,`b`.`id` AS `bid_id`,concat(`e`.`_value`,cast(`a`.`id` as char charset utf8)) AS `bill_no`,`c`.`name` AS `name`,concat(`f`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`amount` AS `amount`,`b`.`apr` AS `apr`,`a`.`title` AS `title`,(select concat(`a`.`periods`,'/',count(`t`.`id`)) from `t_bills` `t` where (`t`.`bid_id` = `a`.`bid_id`)) AS `period`,`a`.`repayment_time` AS `repayment_time`,(case when ((`a`.`repayment_time` - now()) > 0) then '0' else concat(concat(cast(floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) as char(10) charset utf8),'天'),concat(cast(floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) as char charset utf8),'小时'),concat(cast(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) - (floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) * 1440)) - (floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) * 60)) as char charset utf8)),'分') end) AS `overdue_time`,`a`.`real_repayment_time` AS `real_repayment_time`,`g`.`name` AS `supervisor_name`,`g`.`id` AS `supervisor_id`,`c`.`id` AS `user_id` from (((((`t_bills` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_users` `c` on((`b`.`user_id` = `c`.`id`))) join `t_system_options` `e`) join `t_system_options` `f`) join `t_supervisors` `g` on((`g`.`id` = `b`.`manage_supervisor_id`))) where ((`e`.`_key` = 'loan_bill_number') and (`f`.`_key` = 'loan_number') and (`a`.`status` in (0,-(3)))) ";
	//public static final String V_BILL_HASPAYED = "select `a`.`id` AS `id`,year(`a`.`repayment_time`) AS `year`,month(`a`.`repayment_time`) AS `month`,`b`.`id` AS `bid_id`,concat(`e`.`_value`,cast(`a`.`id` as char charset utf8)) AS `bill_no`,`c`.`name` AS `name`,concat(`f`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`amount` AS `amount`,`b`.`apr` AS `apr`,`a`.`title` AS `title`,(select concat(`a`.`periods`,'/',count(`t`.`id`)) from `t_bills` `t` where (`t`.`bid_id` = `a`.`bid_id`)) AS `period`,`a`.`repayment_time` AS `repayment_time`,(case when ((`a`.`repayment_time` - now()) > 0) then '0' else concat(concat(cast(floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) as char(10) charset utf8),'天'),concat(cast(floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) as char charset utf8),'小时'),concat(cast(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) - (floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) * 1440)) - (floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) * 60)) as char charset utf8)),'分') end) AS `overdue_time`,`a`.`real_repayment_time` AS `real_repayment_time`,`g`.`name` AS `supervisor_name`,`g`.`id` AS `supervisor_id`,`c`.`id` AS `user_id` from (((((`t_bills` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_users` `c` on((`b`.`user_id` = `c`.`id`))) join `t_system_options` `e`) join `t_system_options` `f`) join `t_supervisors` `g` on((`g`.`id` = `b`.`manage_supervisor_id`))) where ((`e`.`_key` = 'loan_bill_number') and (`f`.`_key` = 'loan_number') and (`a`.`status` in (0,-(3)))) ";

	/**
	 * 标记逾期的账单详情
	 */
	public static final String V_BILL_DETAIL_FOR_MARK_OVERDUE = "`a`.`id` AS `id`,`b`.`id` AS `bid_id`,`c`.`id` AS `user_id`,`c`.`name` AS `name`,`a`.`notice_count_message` AS `notice_count_message`,`a`.`notice_count_mail` AS `notice_count_mail`,`a`.`notice_count_telphone` AS `notice_count_telephone`,`c`.`mobile` AS `mobile`,`c`.`mobile1` AS `immediate_family_mobile`,`c`.`email` AS `email`,`c`.`email2` AS `immediate_family_email`,`a`.`overdue_fine` AS `ovdedue_fine`,count((`a`.`repayment_corpus` + `a`.`repayment_interest`)) AS `principal_interest_amount`,(select count(`t_bills`.`id`) AS `count(id)` from `t_bills` where ((`t_bills`.`bid_id` = `b`.`id`) and (`t_bills`.`overdue_mark` in (-(1),-(2),-(3))))) AS `overdue_count`,count(((`a`.`repayment_corpus` + `a`.`repayment_interest`) + `a`.`overdue_fine`)) AS `total_pay_amount`,`a`.`overdue_mark` AS `overdue_mark`,`a`.`repayment_time` AS `repayment_time`,cast(floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) as char(10) charset utf8) AS `overdue_time`,concat(`d`.`_value`,cast(`a`.`id` as char charset utf8)) AS `bill_no` from (((`t_bills` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_users` `c` on((`b`.`user_id` = `c`.`id`))) join `t_system_options` `d`) where (`d`.`_key` = 'loan_bill_number') ";

	/**
	 * 收款中的账单详情
	 */
	public static final String V_BILL_DETAIL_FOR_COLLECTION = "`a`.`id` AS `id`,`b`.`id` AS `bid_id`,`c`.`id` AS `user_id`,`c`.`name` AS `name`,`a`.`notice_count_message` AS `notice_count_message`,`a`.`notice_count_mail` AS `notice_count_mail`,`a`.`notice_count_telphone` AS `notice_count_telephone`,`c`.`mobile` AS `mobile`,`c`.`mobile1` AS `immediate_family_mobile`,`c`.`email` AS `email`,`c`.`email2` AS `immediate_family_email`,`a`.`overdue_fine` AS `ovdedue_fine`,(select sum((`t`.`repayment_corpus` + `t`.`repayment_interest`)) AS `SUM(t.repayment_corpus + t.repayment_interest)` from `t_bills` `t` where (`t`.`id` = `a`.`id`)) AS `principal_interest_amount`,(select count(`t_bills`.`id`) AS `count(id)` from `t_bills` where ((`t_bills`.`bid_id` = `a`.`bid_id`) and (`t_bills`.`overdue_mark` in (-(1),-(2),-(3))))) AS `overdue_count`,(select sum(((`t`.`repayment_corpus` + `t`.`repayment_interest`) + `t`.`overdue_fine`)) AS `SUM(t.repayment_corpus + t.repayment_interest + t.overdue_fine)` from `t_bills` `t` where (`t`.`id` = `a`.`id`)) AS `total_pay_amount`,`a`.`overdue_mark` AS `overdue_mark`,concat(`d`.`_value`,cast(`a`.`id` as char charset utf8)) AS `bill_no` from (((`t_bills` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_users` `c` on((`b`.`user_id` = `c`.`id`))) join `t_system_options` `d`) where (`d`.`_key` = 'loan_bill_number') ";

	/**
	 * 账单详情
	 */
	public static final String V_BILL_DETAIL = "`a`.`id` AS `id`,`a`.`status` AS `status`,`b`.`user_id` AS `user_id`,`e`.`name` AS `name`,`b`.`id` AS `bid_id`,`a`.`periods` AS `current_period`,`a`.`repayment_time` AS `repayment_time`,`c`.`name` AS `repayment_type`,`b`.`title` AS `bid_title`,`b`.`amount` AS `loan_amount`,(select count(`t`.`id`) from `t_bills` `t` where (`t`.`bid_id` = `a`.`bid_id`)) AS `loan_periods`,`b`.`apr` AS `apr`,`e`.`reality_name` AS `user_name`,sum((`e`.`balance` + `e`.`freeze`)) AS `user_amount`,`e`.`balance` AS `user_balance`,`b`.`audit_time` AS `produce_bill_time`,sum(((`a`.`repayment_corpus` + `a`.`repayment_interest`) + `a`.`overdue_fine`)) AS `current_pay_amount`,(select sum(((`t1`.`repayment_corpus` + `t1`.`repayment_interest`) + `t1`.`overdue_fine`)) AS `sumall` from `t_bills` `t1` where (`t1`.`bid_id` = `a`.`bid_id`)) AS `loan_principal_interest`,(select count(`t1`.`id`) AS `count(``t1``.``id``)` from `t_bills` `t1` where ((`t1`.`bid_id` = `a`.`bid_id`) and (`t1`.`status` in (-(3),0)))) AS `has_payed_periods`,concat(`d`.`_value`,cast(`a`.`id` as char charset utf8)) AS `bill_number` from ((((`t_bills` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_dict_bid_repayment_types` `c` on((`b`.`repayment_type_id` = `c`.`id`))) join `t_users` `e` on((`b`.`user_id` = `e`.`id`))) join `t_system_options` `d`) where (`d`.`_key` = 'loan_bill_number') ";

	/**
	 * 部门逾期账单
	 */
	public static final String V_BILL_DEPARTMENT_OVERDUE = "`a`.`id` AS `id`,`c`.`id` AS `user_id`,year(`a`.`repayment_time`) AS `year`,month(`a`.`repayment_time`) AS `month`,`b`.`id` AS `bid_id`,concat(`d`.`_value`,cast(`a`.`id` as char charset utf8)) AS `bill_no`,`c`.`name` AS `name`,concat(`e`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`amount` AS `amount`,`b`.`apr` AS `apr`,`a`.`title` AS `title`,(select concat(`a`.`periods`,'/',count(`t`.`id`)) AS `concat(``a``.``periods``,'/',count(``t``.``id``))` from `t_bills` `t` where (`t`.`bid_id` = `a`.`bid_id`)) AS `period`,`a`.`repayment_time` AS `repayment_time`,(case when ((`a`.`repayment_time` - now()) > 0) then '0' else concat(concat(cast(floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) as char(10) charset utf8),'天'),concat(cast(floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) as char charset utf8),'\r\n\r\n小时'),concat(cast(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) - (floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) * 1440)) - (floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) * 60)) as char charset utf8)),'分') end) AS `overdue_time`,(select count(`t_bills`.`id`) AS `count(id)` from `t_bills` where ((`t_bills`.`bid_id` = `b`.`id`) and (`t_bills`.`overdue_mark` in (-(1),-(2),-(3))))) AS `overdue_count`,(select `a`.`name` AS `name` from `t_supervisors` `a` where (`c`.`assigned_to_supervisor_id` = `a`.`id`)) AS `supervisor_name`,(select `a`.`name` AS `name` from `t_supervisors` `a` where (`b`.`manage_supervisor_id` = `a`.`id`)) AS `supervisor_name2` from ((((`t_bills` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_users` `c` on((`b`.`user_id` = `c`.`id`))) join `t_system_options` `d`) join `t_system_options` `e`) where ((`d`.`_key` = 'loan_bill_number') and (`e`.`_key` = 'loan_number') and (`a`.`overdue_mark` = -(2)) and (`b`.`manage_supervisor_id` = 0)) ";

	/**
	 * 部门月到期账单
	 */
	public static final String V_BILL_DEPARTMENT_MONTH_MATURITY = "`a`.`id` AS `id`,`c`.`id` AS `user_id`,year(`a`.`repayment_time`) AS `year`,month(`a`.`repayment_time`) AS `month`,`b`.`id` AS `bid_id`,concat(`d`.`_value`,cast(`a`.`id` as char charset utf8)) AS `bill_no`,`c`.`name` AS `name`,concat(`e`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`amount` AS `amount`,`b`.`apr` AS `apr`,`a`.`title` AS `title`,(select concat(`a`.`periods`,'/',count(`t`.`id`)) from `t_bills` `t` where (`t`.`bid_id` = `a`.`bid_id`)) AS `period`,`a`.`repayment_time` AS `repayment_time`,(case when ((`a`.`repayment_time` - now()) > 0) then '0' else concat(concat(cast(floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) as char(10) charset utf8),'天'),concat(cast(floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) as char charset utf8),'\r\n\r\n小时'),concat(cast(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) - (floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) * 1440)) - (floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) * 60)) as char charset utf8)),'分') end) AS `overdue_time`,(select count(`t_bills`.`id`) AS `count(id)` from `t_bills` where ((`t_bills`.`bid_id` = `b`.`id`) and (`t_bills`.`overdue_mark` in (-(1),-(2),-(3))))) AS `overdue_count`,(select `a`.`name` AS `name` from `t_supervisors` `a` where (`c`.`assigned_to_supervisor_id` = `a`.`id`)) AS `supervisor_name`,(select `a`.`name` AS `name` from `t_supervisors` `a` where (`b`.`manage_supervisor_id` = `a`.`id`)) AS `supervisor_name2` from ((((`t_bills` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_users` `c` on((`b`.`user_id` = `c`.`id`))) join `t_system_options` `d`) join `t_system_options` `e`) where ((`d`.`_key` = 'loan_bill_number') and (`e`.`_key` = 'loan_number') and (year(`a`.`repayment_time`) = year(now())) and (month(`a`.`repayment_time`) = month(now())) and (`a`.`overdue_mark` not in (-(2),-(3))) and (`b`.`manage_supervisor_id` = 0)) ";

	/**
	 * 部门已付账单
	 */
	public static final String V_BILL_DEPARTMENT_HASPAYED = "`a`.`id` AS `id`,`c`.`id` AS `user_id`,year(`a`.`repayment_time`) AS `year`,month(`a`.`repayment_time`) AS `month`,`b`.`id` AS `bid_id`,concat(`e`.`_value`,cast(`a`.`id` as char charset utf8)) AS `bill_no`,`c`.`name` AS `name`,concat(`f`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`amount` AS `amount`,`b`.`apr` AS `apr`,`a`.`title` AS `title`,(select concat(`a`.`periods`,'/',count(`t`.`id`)) from `t_bills` `t` where (`t`.`bid_id` = `a`.`bid_id`)) AS `period`,`a`.`repayment_time` AS `repayment_time`,(case when ((`a`.`repayment_time` - now()) > 0) then '0' else concat(concat(cast(floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) as char(10) charset utf8),'天'),concat(cast(floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) as char charset utf8),'\r\n\r\n小时'),concat(cast(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) - (floor((timestampdiff(MINUTE,`a`.`repayment_time`,now()) / 1440)) * 1440)) - (floor(((timestampdiff(MINUTE,`a`.`repayment_time`,now()) % 1440) / 60)) * 60)) as char charset utf8)),'分') end) AS `overdue_time`,`a`.`real_repayment_time` AS `real_repayment_time`,(select `a`.`name` AS `name` from `t_supervisors` `a` where (`c`.`assigned_to_supervisor_id` = `a`.`id`)) AS `supervisor_name`,(select `a`.`name` AS `name` from `t_supervisors` `a` where (`b`.`manage_supervisor_id` = `a`.`id`)) AS `supervisor_name2` from ((((`t_bills` `a` join `t_bids` `b` on((`a`.`bid_id` = `b`.`id`))) join `t_users` `c` on((`b`.`user_id` = `c`.`id`))) join `t_system_options` `f`) join `t_system_options` `e`) where ((`e`.`_key` = 'loan_bill_number') and (`f`.`_key` = 'loan_number') and (`a`.`status` in (0,-(3))) and (`b`.`manage_supervisor_id` = 0)) ";
	
	/**
	 * 账单
	 */
	public static final String V_BILL_BOARD = "`t_bill_invests`.`id` AS `id`,`t_users`.`name` AS `name`,sum(`t_bill_invests`.`receive_corpus`) AS `corpus`,sum(`t_bill_invests`.`receive_interest`) AS `interest`,count(distinct `t_bill_invests`.`bid_id`) AS `bid_count` from (`t_bill_invests` left join `t_users` on((`t_users`.`id` = `t_bill_invests`.`user_id`))) where (`t_bill_invests`.`status` <> -(7)) ";
	
	/**
	 * 提现类型
	 */
	public static final String[] WITHDRAWAL_TYPE = {"","and w.status = 2 ","and w.status = 0 ",
		"and w.status = 1 ", "and w.status = -1 "};
	
	/**
	 * 后台--提现管理
	 */
	public static final String [] WITHDRAWAL_ORDER_TYPE = {"order by w.time desc", "order by w.time asc", "order by w.time desc",
		"order by w.amount asc", "order by w.amount desc","order by w.audit_time asc", "order by w.audit_time desc",};
	
	/**
	 * VIP排序
	 */
	public static final String [] VIP_USER_ORDER = {"","invest_count asc","invest_count desc","invest_amount asc",
		"invest_amount desc","order_sort asc","order_sort desc","bid_count asc","bid_count desc",
		"bid_amount asc","bid_amount desc","audit_item_count asc","audit_item_count desc"};
	
	/**
	 * 超额借款排序条件
	 */
	public static final String[] OVER_BORROWS_ORDER_CONDITION = 
		{
			" order by time desc",
			" order by time desc",
			" order by appended_items_count desc",
			" order by appended_items_count",
			" order by passed_items_count desc",
			" order by passed_items_count",
			" order by unpassed_items_count desc",
			" order by unpassed_items_count",
			" order by auditing_items_count desc",
			" order by auditing_items_count"
		};
	
	/**
	 * 锁定用户排序
	 */
	public static final String [] LOCKED_USER_ORDER = {"","recharge_amount asc","recharge_amount desc",
		"lock_time asc","lock_time desc","user_amount asc","user_amount desc"};
	
	/**
	 * 用户详情分组排序
	 */
	public static final String V_USER_DETAILS_GROUP_ORDER = "group by `t_users`.`id`,`t_user_details`.`time`,`t_user_details`.`operation`,`t_user_details`.`amount`,`t_user_details`.`balance`,`t_user_details`.`freeze`,`t_user_details`.`recieve_amount`,`t_user_details`.`summary`,`t_user_detail_types`.`name` order by `t_user_details`.`time` desc,`t_user_details`.`id` desc";

	/**
	 * 查询事件关键字
	 * 0 全部, 1 ip地址, 2 操作内容, 3 管理员名字
	 */
	public static final String[] QUERY_EVENT_KEYWORD = {
		" and (t_supervisor_events.ip like ? or t_supervisor_events.descrption like ? or t_supervisors.name like ?) ",
		" and (t_supervisor_events.ip like ?) ",
		" and (t_supervisor_events.descrption like ?) ",
		" and (t_supervisors.name like ?) "
		};
	
	/**
	 * 前台--我的借款账单
	 */
	public static final String [] LOAN_BILL_REPAYMENT = {"", "and status in (-1, -2) ", "and status in (-3, 0) "};
	public static final String [] LOAN_BILL_OVDUE = {"", "and is_overdue in (0) ", "and is_overdue in (-1, -2, -3) "};
	public static final String [] LOAN_BILL_ALL = {"and title like ? ", "and title like ? "};	
	
	/**
	 * 前台--我的理财账单
	 */
	public static final String [] LOAN_INVESTBILL_RECEIVE = {"", "and a.status in (-1, -2,-5,-6) ", "and a.status in (-3 ,-4, 0) "};
	public static final String [] LOAN_INVESTBILL_OVDUE = {"", "and a.status in (-1, 0,-5) ", "and a.status in (-4, -2, -3,-6) "};
	public static final String [] LOAN_INVESTBILL_ALL = {"and a.title like ? ", "and a.title like ? "};	
	
	public static final String [] DEBT_AMOUNT_CONDITION = {" ","  and  t_invest_transfers.debt_amount <= 1000 ",
		"  and   t_invest_transfers.debt_amount > 1000  and  t_invest_transfers.debt_amount <=5000 ","  and   t_invest_transfers.debt_amount > 5000   and  t_invest_transfers.debt_amount <=10000 ",
		"  and   t_invest_transfers.debt_amount > 10000 and   t_invest_transfers.debt_amount <=30000 ","  and   t_invest_transfers.debt_amount > 30000 "};
	
	public static final String [] BID_APR_CONDITION = {" "," and t_bids.apr < 10 ","  and t_bids.apr >= 10 and t_bids.apr <= 15 ",
		" and t_bids.apr >= 15 and t_bids.apr <= 20  "," and t_bids.apr > 20 "};
	
	public static final String [] DEBT_ORDER_CONITION = {" "," order by  debt_amount desc"," order by  debt_amount asc",
		" order by  apr desc", " order by  apr asc", " order by  end_time desc", " order by  end_time asc", " order by  repayment_time desc", "order by  repayment_time asc"};

	public static final String [] TRANSFER_MANAGEMENT_STATUS_CONDITION = {" ", " and t_invest_transfers.status = 0 "," and ( t_invest_transfers.status = 1  or t_invest_transfers.status = 2)"," and t_invest_transfers.status = 3 "," and ( t_invest_transfers.status = -2  or  t_invest_transfers.status = -3  or t_invest_transfers.status = -5)   "," and t_invest_transfers.status = -1 "};
	
	public static final String [] TRANSFER_MANAGEMENT_TYPE_CONDITION = {"   ","  and t_bids.title like ? ","  and t_bids.id like ? "};
	
	public static final String [] BID_AMOUNT_CONDITION = {" "," and t_bids.amount < 100000 ","  and t_bids.amount >= 100000 and t_bids.amount <= 500000  ",
        " and t_bids.amount >= 500000 and t_bids.amount <= 1000000 ","  and t_bids.amount >= 1000000 and t_bids.amount <= 3000000 ",
        "  and t_bids.amount > 3000000 "};
	
	public static final String [] BID_LOAN_SCHEDULE_CONDITION = { " "," and t_bids.loan_schedule < 50 ",
		" and t_bids.loan_schedule >= 50  and t_bids.loan_schedule <=80 "," and t_bids.loan_schedule > 80  and t_bids.loan_schedule <= 100 "," and t_bids.loan_schedule = 100 "};

	public static final String [] BID_PERIOD_CONDITION = { " ","< 31", "between 31 and 90","between 90 and 180","between 180 and 365",">365"};
	public static final String [] BID_STATUS_CONDITION = { " "," and t_bids.status in(1,2) and t_bids.TIME > now()", " and t_bids.status in(1,2) and t_bids.TIME <= now() "," and t_bids.status = 4"," and t_bids.status = 5"};
	public static final String [] BID_MIN_INVEST_AMOUNT_CONDITION = {" "," and t_bids.min_invest_amount < 1000 ","  and t_bids.min_invest_amount >= 1000 and t_bids.min_invest_amount <= 10000  ",
			" and t_bids.min_invest_amount >= 10000 and t_bids.min_invest_amount <= 50000 ","  and t_bids.min_invest_amount >= 50000 and t_bids.min_invest_amount <= 100000 ",
			"  and t_bids.min_invest_amount > 100000 "};


	/**
	 * 前台--cps推广
	 */
	public static final String[] MY_CPS = {"","and t_users.is_active = 1 ","and t_users.is_active = 0 "};
	
	/**
	 * 合作机构
	 */
	public static final String V_AGENCIES = "select concat(`e`.`_value`,cast(`a`.`id` as char charset utf8)) AS `no`,`a`.`id` AS `id`,`a`.`name` AS `name`,`a`.`credit_level` AS `credit_level`,(select count(`b`.`id`) AS `bid_count` from `t_bids` `b` where (`b`.`agency_id` = `a`.`id`)) AS `bid_count`,(select avg(`b`.`apr`) AS `bid_avg_apr` from `t_bids` `b` where (`b`.`agency_id` = `a`.`id`)) AS `bid_avg_apr`,(select count(`b`.`id`) AS `success_bid_count` from `t_bids` `b` where ((`b`.`agency_id` = `a`.`id`) and (`b`.`status` = 4))) AS `success_bid_count`,(select count(`b`.`id`) AS `overdue_bid_count` from (`t_bids` `b` join `t_bills` `bl`) where ((`b`.`agency_id` = `a`.`id`) and (`bl`.`bid_id` = `b`.`id`) and (`bl`.`overdue_mark` in (-(1),-(2))))) AS `overdue_bid_count`,(select count(`b`.`id`) AS `bad_bid_count` from (`t_bids` `b` join `t_bills` `bl`) where ((`b`.`agency_id` = `a`.`id`) and (`bl`.`bid_id` = `b`.`id`) and (`bl`.`overdue_mark` = -(3)))) AS `bad_bid_count`,`a`.`time` AS `time`,(to_days(now()) - to_days(`a`.`time`)) AS `cooperation_length`,`a`.`is_use` AS `is_use` from (`t_agencies` `a` join `t_system_options` `e`) where (`e`.`_key` = 'agencies_number')";
	
	/**
	 * 审核资料用户统计
	 */
	//public static final String V_AUDIT_ITEM_USER = "select `u`.`id` AS `id`,`u`.`name` AS `user_name`,`u`.`email` AS `email`,`u`.`mobile` AS `mobile`,`u`.`time` AS `time`,`l`.`image_filename` AS `image_filename`,(select count(`udi`.`id`) AS `sum_count` from `t_user_audit_items` `udi` where (`udi`.`user_id` = `u`.`id`)) AS `sum_count`,(select count(`udi`.`id`) AS `audited_count` from `t_user_audit_items` `udi` where ((`udi`.`user_id` = `u`.`id`) and (`udi`.`status` = 2))) AS `audited_count`,(select count(`udi`.`id`) AS `not_pass_count` from `t_user_audit_items` `udi` where ((`udi`.`user_id` = `u`.`id`) and (`udi`.`status` = -(1)))) AS `not_pass_count`,(select count(`udi`.`id`) AS `auditing_count` from `t_user_audit_items` `udi` where ((`udi`.`user_id` = `u`.`id`) and (`udi`.`status` = 1))) AS `auditing_count`,(select count(`b`.`id`) AS `bid_success_count` from `t_bids` `b` where ((`b`.`user_id` = `u`.`id`) and (`b`.`status` in (4,5)))) AS `bid_success_count`,(select count(`b`.`id`) AS `bid_loaning_count` from `t_bids` `b` where ((`b`.`user_id` = `u`.`id`) and (`b`.`status` in (1,2)))) AS `bid_loaning_count`,(select count(`i`.`id`) AS `invest_count` from `t_invests` `i` where (`i`.`user_id` = `u`.`id`)) AS `invest_count` from (`t_users` `u` left join `t_credit_levels` `l` on((`l`.`id` = `u`.`credit_level_id`)))";
	
	/**
	 * 标记
	 */
	public static final String V_BIDS = "select concat(`e`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`id` AS `id`,`u`.`id` AS `user_id`,`u`.`name` AS `user_name`,`b`.`product_id` AS `product_id`,`p`.`name` AS `product_name`,`p`.`credit_id` AS `credit_id`,`b`.`image_filename` AS `bid_image_filename`,`p`.`small_image_filename` AS `small_image_filename`,`b`.`title` AS `title`,`b`.`loan_schedule` AS `loan_schedule`,`b`.`apr` AS `apr`,`b`.`amount` AS `amount`,`b`.`period_unit` AS `period_unit`,`b`.`period` AS `period`,`b`.`has_invested_amount` AS `has_invested_amount`,`b`.`is_quality` AS `is_quality`,`b`.`is_hot` AS `is_hot`,`b`.`status` AS `status`,`b`.`is_agency` AS `is_agency`,`b`.`agency_id` AS `agency_id`,`b`.`repayment_type_id` AS `repayment_type_id`,`b`.`is_show_agency_name` AS `is_show_agency_name`,`b`.`bonus_type` AS `bonus_type`,`b`.`bonus` AS `bonus`,`b`.`award_scale` AS `award_scale`,`a`.`name` AS `agency_name`,`b`.`time` AS `time`,`r`.`name` AS `repayment_type_name`,`b`.`mark` AS `mark`,c.order_sort AS order_sort,c.image_filename AS credit_level_image_filename,`f_user_audit_item`(`u`.`id`,`b`.`mark`,2) AS `user_item_count_true`,`f_user_audit_item`(`u`.`id`,`b`.`mark`,-(1)) AS `user_item_count_false`,(select count(`pail`.`id`) AS `product_item_count` from `t_product_audit_items_log` `pail` where ((`pail`.`mark` = `b`.`mark`) and (`pail`.`type` = 1))) AS `product_item_count`,(select count(`bl`.`id`) AS `overdue_count` from `t_bills` `bl` where ((`bl`.`bid_id` = `b`.`id`) and (`bl`.`overdue_mark` in (-(1),-(2),-(3))))) AS `overdue_count` from ((((((`t_bids` `b` left join `t_products` `p` on((`b`.`product_id` = `p`.`id`))) left join `t_users` `u` on((`u`.`id` = `b`.`user_id`))) LEFT JOIN t_credit_levels c ON ((u.credit_level_id = c.id))) left join `t_agencies` `a` on((`b`.`agency_id` = `a`.`id`))) left join `t_dict_bid_repayment_types` `r` on((`r`.`id` = `b`.`repayment_type_id`))) join `t_system_options` `e`) where (`e`.`_key` = 'loan_number') order by `b`.`time` desc";
	
	/**
	 * 标分配
	 */
	//public static final String V_BID_ASSIGNED = "select concat(`e`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`id` AS `id`,`b`.`title` AS `title`,`u`.`name` AS `user_name`,`b`.`user_id` AS `user_id`,`b`.`amount` AS `amount`,`p`.`name` AS `product_name`,`p`.`small_image_filename` AS `small_image_filename`,`b`.`apr` AS `apr`,`b`.`period` AS `period`,`b`.`period_unit` AS `period_unit`,`b`.`audit_time` AS `audit_time`,`b`.`manage_supervisor_id` AS `manage_supervisor_id`,`s`.`name` AS `supervisor_name`,`r`.`id` AS `repaymentId` from (((((`t_bids` `b` left join `t_users` `u` on((`b`.`user_id` = `u`.`id`))) left join `t_products` `p` on((`b`.`product_id` = `p`.`id`))) left join `t_dict_bid_repayment_types` `r` on((`r`.`id` = `b`.`repayment_type_id`))) join `t_supervisors` `s` on((`s`.`id` = `b`.`manage_supervisor_id`))) join `t_system_options` `e`) where ((`b`.`manage_supervisor_id` > 0) and (`e`.`_key` = 'loan_number'))";
	
	/**
	 * 收藏标
	 */
	public static final String V_BID_ATTENTION = "concat(`e`.`_value`,cast(`uab`.`bid_id` as char charset utf8)) AS `bid_no`,`b`.`id` AS `id`,`b`.`title` AS `title`,`b`.`product_id` AS `product_id`,`p`.`small_image_filename` AS `small_image_filename`,`uab`.`bid_id` AS `bid_id`,`uab`.`user_id` AS `user_id`,`u`.`name` AS `user_name`,`b`.`amount` AS `amount`,`b`.`period` AS `period`,`b`.`period_unit` AS `period_unit`,`b`.`apr` AS `apr`,`b`.`status` AS `status`,`b`.`mark` AS `mark`,`b`.`repayment_type_id` AS `repaymentId`,`f_user_audit_item`(`u`.`id`,`b`.`mark`,2) AS `user_item_count_true`,(select count(`pail`.`id`) AS `product_item_count` from `t_product_audit_items_log` `pail` where ((`pail`.`mark` = `b`.`mark`) and (`pail`.`type` = 1))) AS `product_item_count` from (((((`t_user_attention_bids` `uab` left join `t_bids` `b` on((`uab`.`bid_id` = `b`.`id`))) left join `t_dict_bid_repayment_types` `r` on((`r`.`id` = `b`.`repayment_type_id`))) left join `t_products` `p` on((`b`.`product_id` = `p`.`id`))) left join `t_users` `u` on((`uab`.`user_id` = `u`.`id`))) join `t_system_options` `e`) where (`e`.`_key` = 'loan_number')";
	
	/**
	 * 待验证的借款标 
	 */
	public static final String V_BID_WAIT_VERIFY = "SELECT concat(`e`.`_value`, cast(`b`.`id` AS CHAR charset utf8))AS `bid_no`,`b`.`id` AS `id`,`b`.`title` AS `title`,`b`.`user_id` AS `user_id`,`u`.`name` AS `user_name`,`b`.`product_id` AS `product_id`,`p`.`small_image_filename` AS `small_image_filename`,`p`.`name` AS `product_name`,`b`.`apr` AS `apr`,`b`.`period_unit` AS `period_unit`,`b`.`period` AS `period`,`b`.`time` AS `time`,`b`.`amount` AS `amount`,`b`.`status` AS `status`,`b`.`mark` AS `mark`,`f_credit_levels`(`u`.`id`)AS `credit_level_id`,`f_user_audit_item`(`u`.`id`, `b`.`mark`, 2)AS `user_item_count_true`,`f_user_audit_item`(`u`.`id`, `b`.`mark` ,-(1))AS `user_item_count_false`,`b`.`repayment_type_id` AS `repaymentId`,(SELECT count(`pail`.`id`)AS `product_item_count` FROM`t_product_audit_items_log` `pail` WHERE `pail`.`mark` = `b`.`mark` AND `pail`.`type` = 1)AS `product_item_count` FROM `t_bids` `b` LEFT JOIN `t_users` `u` ON `b`.`user_id` = `u`.`id` LEFT JOIN `t_products` `p` ON `b`.`product_id` = `p`.`id` JOIN `t_system_options` `e` WHERE(`b`.`status` = 10 OR b.`status` = 11 OR b.`status` = 12) AND `e`.`_key` = 'loan_number'";

	/**
	 * 根据id查询对应标
	 */
	public static final String V_BI_ID = "SELECT `b`.`id` AS `bid_no`,`b`.`id` AS `id`,`b`.`title` AS `title`,`b`.`user_id` AS `user_id`,`b`.`apr` AS `apr`,`b`.`period_unit` AS `period_unit`,`b`.`period` AS `period`,`b`.`time` AS `time`,`b`.`amount` AS `amount`,`b`.`status` AS `status`,`b`.`mark` AS `mark`,`b`.`repayment_type_id` AS `repaymentId`  FROM `t_bids` `b` where 1=1";

	/**
	 * 审核中的借款标
	 */
	public static final String V_BID_AUDITING = "select concat(`e`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`id` AS `id`,`b`.`title` AS `title`,`b`.`user_id` AS `user_id`,`u`.`name` AS `user_name`,`b`.`product_id` AS `product_id`,`p`.`small_image_filename` AS `small_image_filename`,`p`.`name` AS `product_name`,`b`.`apr` AS `apr`,`b`.`period_unit` AS `period_unit`,`b`.`period` AS `period`,`b`.`time` AS `time`,`b`.`amount` AS `amount`,`b`.`status` AS `status`,`b`.`mark` AS `mark`,`c`.`order_sort` AS `order_sort`,`c`.`image_filename` AS `credit_level_image_filename`,`f_user_audit_item`(`u`.`id`,`b`.`mark`,2) AS `user_item_count_true`,`f_user_audit_item`(`u`.`id`,`b`.`mark`,-(1)) AS `user_item_count_false`,`b`.`repayment_type_id` AS `repaymentId`,(select count(`pail`.`id`) AS `product_item_count` from `t_product_audit_items_log` `pail` where ((`pail`.`mark` = `b`.`mark`) and (`pail`.`type` = 1))) AS `product_item_count` from (((((`t_bids` `b` left join `t_users` `u` on((`b`.`user_id` = `u`.`id`))) left join `t_products` `p` on((`b`.`product_id` = `p`.`id`))) LEFT JOIN `t_credit_levels` `c` ON ((`u`.`credit_level_id` = `c`.`id`))) join `t_system_options` `e`)) where ((`b`.`status` = 0) and (`e`.`_key` = 'loan_number'))";
	
	/**
	 * 坏账中的借款标
	 */
	public static final String V_BID_BAD = "select concat(`e`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`id` AS `id`,`b`.`time` AS `time`,`b`.`title` AS `title`,`b`.`user_id` AS `user_id`,`u`.`name` AS `user_name`,`b`.`amount` AS `amount`,`b`.`product_id` AS `product_id`,`p`.`small_image_filename` AS `small_image_filename`,`p`.`name` AS `product_name`,`b`.`apr` AS `apr`,`b`.`period_unit` AS `period_unit`,`b`.`period` AS `period`,`b`.`status` AS `status`,`b`.`audit_time` AS `audit_time`,`b`.`last_repay_time` AS `last_repay_time`,`b`.`manage_supervisor_id` AS `manage_supervisor_id`,`s`.`name` AS `supervisor_name`,c.image_filename AS credit_level_image_filename,sum(floor((timestampdiff(MINUTE,`bl`.`mark_overdue_time`,now()) / 1440))) AS `overdue_length`,max(`bl`.`mark_bad_time`) AS `mark_bad_time`,(select count(`bl`.`id`) AS `repayment_count` from `t_bills` `bl` where ((`bl`.`bid_id` = `b`.`id`) and (`bl`.`status` in (-(2),-(3),0)))) AS `repayment_count`,(select count(`bl`.`id`) AS `overdue_count` from `t_bills` `bl` where ((`bl`.`bid_id` = `b`.`id`) and (`bl`.`overdue_mark` in (-(2),-(3),-(1))))) AS `overdue_count` from ((((((`t_bids` `b` left join `t_users` `u` on((`b`.`user_id` = `u`.`id`))) LEFT JOIN t_credit_levels c ON ((u.credit_level_id = c.id))) left join `t_products` `p` on((`b`.`product_id` = `p`.`id`))) left join `t_bills` `bl` on((`bl`.`bid_id` = `b`.`id`))) left join `t_supervisors` `s` on((`s`.`id` = `b`.`manage_supervisor_id`))) join `t_system_options` `e`) where ((`b`.`status` in (4,5)) and (`e`.`_key` = 'loan_number') and (`bl`.`overdue_mark` = -(3))) group by `b`.`id`";
	
	/**
	 * 借款中的借款标
	 */
	public static final String V_BID_FUNDRAISEING = "select concat(`e`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`id` AS `id`,`b`.`title` AS `title`,`b`.`user_id` AS `user_id`,`b`.`invest_expire_time` AS `invest_expire_time`,`b`.`real_invest_expire_time` AS `real_invest_expire_time`,`u`.`name` AS `user_name`,`b`.`amount` AS `amount`,`b`.`product_id` AS `product_id`,`p`.`name` AS `product_name`,`p`.`small_image_filename` AS `small_image_filename`,`b`.`apr` AS `apr`,`b`.`period_unit` AS `period_unit`,`b`.`period` AS `period`,`b`.`time` AS `time`,`b`.`status` AS `status`,`b`.`loan_schedule` AS `loan_schedule`,`b`.`has_invested_amount` AS `has_invested_amount`,`b`.`audit_time` AS `audit_time`,`b`.`mark` AS `mark`,c.image_filename AS credit_level_image_filename,c.order_sort AS `order_sort`,`f_user_audit_item`(`u`.`id`,`b`.`mark`,2) AS `user_item_count_true`,`f_user_audit_item`(`u`.`id`,`b`.`mark`,-(1)) AS `user_item_count_false`,`b`.`repayment_type_id` AS `repaymentId`,(to_days(`b`.`real_invest_expire_time`) - to_days((case when (`b`.`status` = 1) then `b`.`time` when (`b`.`status` = 2) then `b`.`audit_time` end))) AS `full_days`,(select count(`pail`.`id`) AS `product_item_count` from `t_product_audit_items_log` `pail` where ((`pail`.`mark` = `b`.`mark`) and (`pail`.`type` = 1))) AS `product_item_count` from (((((`t_bids` `b` left join `t_users` `u` on((`b`.`user_id` = `u`.`id`))) LEFT JOIN t_credit_levels c ON ((u.credit_level_id = c.id))) left join `t_products` `p` on((`b`.`product_id` = `p`.`id`))) join `t_system_options` `e`)) where ((`b`.`status` in (1,2)) and (`e`.`_key` = 'loan_number'))";
	
	/**
	 * 失败的借款标
	 */
	public static final String V_BID_NOT_THROUGH = "select concat(`e`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`id` AS `id`,`b`.`title` AS `title`,`b`.`user_id` AS `user_id`,`u`.`name` AS `user_name`,`b`.`product_id` AS `product_id`,`p`.`small_image_filename` AS `small_image_filename`,`p`.`name` AS `product_name`,`b`.`apr` AS `apr`,`b`.`period_unit` AS `period_unit`,`b`.`period` AS `period`,`b`.`time` AS `time`,`b`.`amount` AS `amount`,`b`.`status` AS `status`,`b`.`mark` AS `mark`,c.image_filename AS credit_level_image_filename,`f_user_audit_item`(`u`.`id`,`b`.`mark`,2) AS `user_item_count_true`, b.repayment_type_id as repaymentId, (select count(`pail`.`id`) AS `product_item_count` from `t_product_audit_items_log` `pail` where ((`pail`.`mark` = `b`.`mark`) and (`pail`.`type` = 1))) AS `product_item_count` from ((((`t_bids` `b` left join `t_users` `u` on((`b`.`user_id` = `u`.`id`))) LEFT JOIN t_credit_levels c ON ((u.credit_level_id = c.id))) left join `t_products` `p` on((`b`.`product_id` = `p`.`id`))) join `t_system_options` `e`) where ((`b`.`status` in (-(1),-(2),-(3),-(4),-(5),-(10))) and (`e`.`_key` = 'loan_number'))";
	
	/**
	 * 逾期的借款标
	 */
	public static final String V_BID_OVERDUE = "select concat(`e`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`id` AS `id`,`b`.`time` AS `time`,`b`.`title` AS `title`,`b`.`user_id` AS `user_id`,`u`.`name` AS `user_name`,`b`.`amount` AS `amount`,`b`.`product_id` AS `product_id`,`p`.`small_image_filename` AS `small_image_filename`,`p`.`name` AS `product_name`,`b`.`apr` AS `apr`,`b`.`period_unit` AS `period_unit`,`b`.`period` AS `period`,`b`.`status` AS `status`,`b`.`audit_time` AS `audit_time`,`b`.`repayment_type_id` AS `repayment_type_id`,`r`.`name` AS `repayment_type_name`,min(`bl`.`mark_overdue_time`) AS `mark_overdue_time`,c.image_filename AS credit_level_image_filename,(select count(`bl`.`id`) AS `repayment_count` from `t_bills` `bl` where ((`bl`.`bid_id` = `b`.`id`) and (`bl`.`status` in (-(3),0)))) AS `repayment_count`,(select count(`bl`.`id`) AS `overdue_count` from `t_bills` `bl` where ((`bl`.`bid_id` = `b`.`id`) and (`bl`.`overdue_mark` in (-(1),-(2),-(3))))) AS `overdue_count` from ((((((`t_bids` `b` left join `t_users` `u` on((`b`.`user_id` = `u`.`id`))) LEFT JOIN t_credit_levels c ON ((u.credit_level_id = c.id))) left join `t_products` `p` on((`b`.`product_id` = `p`.`id`))) left join `t_dict_bid_repayment_types` `r` on((`r`.`id` = `b`.`repayment_type_id`))) left join `t_bills` `bl` on((`bl`.`bid_id` = `b`.`id`))) join `t_system_options` `e`) where ((`b`.`status` = 4) and (`bl`.`overdue_mark` in (-(1),-(2),-(3))) and (`e`.`_key` = 'loan_number')) group by `b`.`id`";
	
	/**
	 * 标的提问记录
	 */
	public static final String V_BID_QUESTIONS = "SELECT q.id AS id, b.title AS title, q.user_id AS user_id, u.name as user_name, q.time AS time, q.bid_id AS bid_id, q.content AS content, q.is_answer AS is_answer, q.questioned_user_id as questioned_user_id FROM t_bid_questions q LEFT JOIN t_bids b ON q.bid_id = b.id LEFT JOIN  t_users u ON u.id = q.user_id";
	
	/**
	 * 待放款的借款标
	 */
	public static final String V_BID_RELEASE_FUNDS = "select concat(`e`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`id` AS `id`,`b`.`title` AS `title`,`b`.`user_id` AS `user_id`,`b`.`period_unit` AS `period_unit`,`b`.`period` AS `period`,`u`.`name` AS `user_name`,`u`.`email` AS `email`,`b`.`product_id` AS `product_id`,`p`.`name` AS `product_name`,`p`.`small_image_filename` AS `small_image_filename`,`b`.`apr` AS `apr`,`b`.`time` AS `time`,`b`.`real_invest_expire_time` AS `real_invest_expire_time`,`b`.`amount` AS `amount`,`b`.`status` AS `status`,`b`.`audit_time` AS `audit_time`,`b`.`allocation_supervisor_id` AS `allocation_supervisor_id`,`b`.`bank_account_id` AS `bank_account_id`,`s`.`name` AS `supervisor_name`,`b`.`mark` AS `mark`,`f_user_audit_item`(`u`.`id`,`b`.`mark`,2) AS `user_item_count_true`,`b`.`repayment_type_id` AS `repaymentId`,c.image_filename AS credit_level_image_filename,(select count(`pail`.`id`) AS `product_item_count` from `t_product_audit_items_log` `pail` where ((`pail`.`mark` = `b`.`mark`) and (`pail`.`type` = 1))) AS `product_item_count` from ((((((`t_bids` `b` left join `t_users` `u` on((`b`.`user_id` = `u`.`id`))) LEFT JOIN t_credit_levels c ON ((c.id = u.credit_level_id))) left join `t_products` `p` on((`b`.`product_id` = `p`.`id`))) left join `t_supervisors` `s` on((`s`.`id` = `b`.`allocation_supervisor_id`))) join `t_system_options` `e`)) where ((`b`.`status` in (3,4)) and (`e`.`_key` = 'loan_number'))";
	
	/**
	 * 已成功的借款标
	 */
	public static final String V_BID_REPAYMENT = "select concat(`e`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`id` AS `id`,`b`.`title` AS `title`,`b`.`user_id` AS `user_id`,`b`.`time` AS `time`,`u`.`name` AS `user_name`,`b`.`amount` AS `amount`,`b`.`product_id` AS `product_id`,`p`.`small_image_filename` AS `small_image_filename`,`p`.`name` AS `product_name`,`b`.`apr` AS `apr`,`b`.`period_unit` AS `period_unit`,`b`.`period` AS `period`,`b`.`status` AS `status`,`b`.`audit_time` AS `audit_time`,`b`.`last_repay_time` AS `last_repay_time`,`dbrt`.`name` AS `repayment_type_name`,`b`.`manage_supervisor_id` AS `manage_supervisor_id`,`s`.`name` AS `supervisor_name`,c.image_filename AS credit_level_image_filename,(select sum(((`bi`.`repayment_corpus` + `bi`.`repayment_interest`) + `bi`.`overdue_fine`)) AS `capital_interest_sum` from `t_bills` `bi` where (`bi`.`bid_id` = `b`.`id`)) AS `capital_interest_sum`,(select count(`bl`.`id`) AS `repayment_count` from `t_bills` `bl` where ((`bl`.`bid_id` = `b`.`id`) and (`bl`.`status` in (0,-(3))))) AS `repayment_count`,(select count(`bl`.`id`) AS `overdue_count` from `t_bills` `bl` where ((`bl`.`bid_id` = `b`.`id`) and (`bl`.`overdue_mark` in (-(3),-(1),-(2))))) AS `overdue_count` from ((((((`t_bids` `b` left join `t_users` `u` on((`b`.`user_id` = `u`.`id`))) LEFT JOIN t_credit_levels c ON ((u.credit_level_id = c.id))) left join `t_products` `p` on((`b`.`product_id` = `p`.`id`))) left join `t_dict_bid_repayment_types` `dbrt` on((`dbrt`.`id` = `b`.`repayment_type_id`))) left join `t_supervisors` `s` on((`s`.`id` = `b`.`manage_supervisor_id`))) join `t_system_options` `e`) where ((`b`.`status` = 5) and (`e`.`_key` = 'loan_number'))";
	
	/**
	 * 还款中的借款标
	 */
	public static final String V_BID_REPAYMENTING = "select concat(`e`.`_value`,cast(`b`.`id` as char charset utf8)) AS `bid_no`,`b`.`id` AS `id`,`b`.`time` AS `time`,`b`.`title` AS `title`,`b`.`user_id` AS `user_id`,`u`.`name` AS `user_name`,`b`.`amount` AS `amount`,`b`.`product_id` AS `product_id`,`p`.`name` AS `product_name`,`p`.`small_image_filename` AS `small_image_filename`,`b`.`apr` AS `apr`,`b`.`period_unit` AS `period_unit`,`b`.`period` AS `period`,`b`.`status` AS `status`,`b`.`audit_time` AS `audit_time`,`b`.`repayment_type_id` AS `repayment_type_id`,`r`.`name` AS `repayment_type_name`,`b`.`manage_supervisor_id` AS `manage_supervisor_id`,`s`.`name` AS `supervisor_name`,c.image_filename AS credit_level_image_filename,c.order_sort AS `order_sort`,(select count(`bl`.`id`) AS `repayment_count` from `t_bills` `bl` where ((`bl`.`bid_id` = `b`.`id`) and (`bl`.`status` in (0,-(3))))) AS `repayment_count`,(select count(`bl`.`id`) AS `overdue_count` from `t_bills` `bl` where ((`bl`.`bid_id` = `b`.`id`) and (`bl`.`overdue_mark` in (-(3),-(1),-(2))))) AS `overdue_count`,(select `bi`.`repayment_time` AS `repayment_time` from `t_bills` `bi` where ((month(now()) <= month(`bi`.`repayment_time`)) and (`bi`.`bid_id` = `b`.`id`)) group by `bi`.`bid_id`) AS `repayment_time` from ((((((`t_bids` `b` left join `t_users` `u` on((`b`.`user_id` = `u`.`id`))) LEFT JOIN t_credit_levels c ON ((u.credit_level_id = c.id))) left join `t_products` `p` on((`b`.`product_id` = `p`.`id`))) left join `t_dict_bid_repayment_types` `r` on((`r`.`id` = `b`.`repayment_type_id`))) left join `t_supervisors` `s` on((`s`.`id` = `b`.`manage_supervisor_id`))) join `t_system_options` `e`) where ((`b`.`status` = 4 or `b`.`status` = 14) and (`e`.`_key` = 'loan_number'))";

	/**
	 * 会员资料审核管理统计
	 */
	public static final String V_USER_AUDIT_ITEM_STATS = "SELECT a.id, a.`name` as user_name,a.time,a.email,a.mobile,b.sum_count,b.audited_count,b.not_pass_count,b.auditing_count,b.bid_success_count,b.bid_loaning_count,b.invest_count,c.image_filename AS credit_level_image_filename FROM ((t_users a LEFT JOIN t_statistic_user_audit_items b ON (a.id = b.user_id)) LEFT JOIN t_credit_levels c ON ((a.credit_level_id = c.id)))";
}